- 1 Vlookup function
- 1.1 Vlookup definition:
- 1.2 Vlookup in same worksheet:
- 1.3 Vlookup from another sheet:
- 1.4 The below areas can be used the vlookup function :
- 1.5 Vlookup advantages:
- 1.6 Named Range In Excel:
- 1.7 Vlookup disadvantages ( Limitations ):
- 1.8 Vlookup with duplicate values:
- 1.9 Vlookup Meaning:
This is the best formula among all excel formulas. Hence you are going to learn this formula in detailed in this article. Also you are going to know all the vlookup advantages and disadvantages ( Limitations ), definition,Syntax, Approximate Match, Exactmatch, which areas can be used the vlookup, Vlookup in same worksheet, Vlookup with duplicates, Vlookup different sheet, Named range in Vlookup, How to create Named range in excel and Vlookup Meaning.Vlookup full form is vertical lookup, Unless we follow the syntax, we get the result as vlookup Errors
=vlookup(Lookup-value, Table-Array, Column-index-number,Exactmatch or Approximatematch)
Note: Always it is better to sort the values of the data in ascending or descending depending on your requirement to get the results else there might be chance to get unexpected results by this formula.
Vlookup in same worksheet:
Vlookup Syntax Explanation:
Lookup Value: G4 cell is the Lookup value in the below example
Table Array: C column and D columns are Table array
Column index Number: Since we select only two columns C and D. And we require the 2nd column values as results, we are going to give the column index number as 2
Exact match: 99.99% of time we regularly use the exact-match that is denoted by zero ‘0’. Here in the below example you can find the exact-match only.
Vlookup from another sheet:
It is also same as example 1, only one difference is that you need to add the sheet name to the table array in the below syntax. Here in the below example you can find the data is in the vlookup data sheet. And result in the Vlookup result sheet. Hence the syntax would be as shown in the below picture.
It has the change that is ‘Vlookup Data’!
Vlookup from different Sheet as shown in the below
Approximate match: Very rarely we use this approximate-match that is denoted by ‘1’
The below areas can be used the vlookup function :
Userforms, subroutines, modules, textbox, listbox, combobox, worksheets (with in the same worksheets,), different excel worksheets (means one sheet to another worksheet with in the same workbook), different excel workbooks and User defined functions
- Mainly this function can be used for the purpose of searching values for the respective lookup values.
- VLOOKUP is simple formula and very powerful in excel spreadsheet
- Many Excel formula can be used within the and along with VLOOKUP
- Assigning the right values for the given lookup values as I have explained you in the approximate match example in the above.
- We can customize the function by taking the support of the other excel functions like countif, sumif, count, countifs, sumifs, and sum function. This function can be used with the combination of the sumproduct, if function, nested if function, Indirect, index and match functions.
- VLOOKUP can be customized in the user defined functions as and when we require. This is one of the major advantages.
- This function can be used for Pivot tables and tables also to get the exact values
- We can use the named ranges for range criteria in the vlookup syntax as shown in the below
Named Range in the Vlookup:
Named range is nothing but a collections of cells within the range where you can provide the name for that specific range
Named Range In Excel:
Vlookup disadvantages ( Limitations ):
- Vlookup can not get the right value when there are duplicates in the lookup value related column. We must need to sort the data when there are duplicate values in the lookup value related column. Then this function can return the first value related value This is the major drawback of this function. Of course we can overcome this problem by using the different functions in the excel workbooks
Vlookup with duplicate values:
2. Using this function we can only pull the right side values as we discussed in the above. If we need to pull the left side values based on the right side value. We can use the reverse vlookup function. This is the combination of the index and match functions.
3.It only works on columns if we need to use for the rows then we have to use the Hlookup function which is very similar to Vlookup function. Hence reverse vlookup which is the combination of the index and match functions.
4. Vlookup major disadvantage is when you have more vlookup formulas within Spreadsheet or within one Excel workbook, it hangs a lot since it is an array formula. When user getting hanged in the workbook that time user need to setup formulas calculation option as manual, you can find the below screen shot to setup manual option in the excel.
Vlookup significance is nothing but a Vertical lookup where you can search the data vertically based on the lookup values availability. As I have explained you in the above examples. Vlookup is one of the key functions within the excel Spreadsheet. Almost many MIS tasks get complete with only with this function. Still this has some limitations as we have already discussed in the above. But we can overcome this lose by taking the support of the other excel functions. Here in the excel Vlookup point of view vertical in the sense column by column
Column A, Column B wise from left to right we can search the values using this VLOOKUP formula in Excel spreadsheets. Please observe the below Screenshot for better understanding
There are many wonderful examples can be derived by Vlookup in the live projects, those also we are trying to include in this article at the earliest.
This entire article can be considered as Vlookup tutorial
See Also :