Vlookup Function: Explained with Examples
Usage of Vlookup function (formula) in Microsoft Excel 2003,2007,2010,2013 has many advantages/benefits in data analysis applications. This step by step tutorial will explain Vlookup syntax and Vlookup command purpose and limitations with examples. This is the reference to know 1. Vlookup Definition, Syntax, keyboard shortcuts, 2. Sample Data and Examples, 3. Interactive Practical Worksheet for Practice (in .xls and .xlsx format), 4. Vlookup using Two or multiple Conditions/criteria, 5. Vlookup between two columns, worksheets,workbooks, 6. Vlookup to find duplicates and show it as yes or no, 7. Vlookup and.vs Hlookup, Lookup, Match, Offset, 8. And Vlookup using VBA.
What is the use of VLOOKUP function?
V in VLOOKUP stands for Vertical. It looks for the lookup_value in vertically in a given array and results the respective value from the specified column. You can also mention if you want to look for exact match.
Exact match returns the value if the lookup value exists in a given array, it returens #NA if it can’t find the lookup_value in the given range or array. And the other option is Approximate match, it search for exact lookup_value and it returens the repective result for the lookup value. It returns the near by results if it can not find the exact match for the lookup_value..
Vlookup always looks at right side. That means, It looks up for the lookup values in the left most column of the given array and search for the result value in the right side columns as specified in the column index.
VLOOKUP function Definition
Here is the definition of Vlookup funtion: VLOOKUP Function in Excel searches for value and returns the value in a given column that matches a value in the left most column of a table.
Syntax and Logic of VLOOKUP function
Here is the syntax and Logic of the vlookup function:
=VLOOKUP(lookup_value, table_array, col_index_num
lookup_value
: lookup_value is the value to be searched in the first column of the array.
table_array
: table_array is the range or a range name containing the table of data.
col_index_num
: col_index_num is the column number in table_array from which you return corresponding matching value
[range_lookup]
: range_lookup is to specify whether to find an exact match or partial match
True or 1 = Closest match is returned
False or 0 = Exact matches are returned
Vlookup Help
You can go to the Formulas menu and select Lookup & Reference command, this will show you list of Lookup functions and then choose Vlookup function to open the formula help dialog-box. alternatively, you can use the keyword shortcut Alt+M then O to insert the Lookup functions. Here is the Vlookup Formula help dialog-box, this will help you to choose the function parameters.
Examples on VLOOKUP Function in Excel
Let us see some examples on Vlookup Function to understand the Vlookup usage and advantages in day to day work in MS Excel 2003,2007,2010,2013.
Example 1:
=VLOOKUP(4,A5:C9,2,FALSE)
The VLOOKUP formula Looks for 4 (lookup_vlue) in the left most column of the lookup range (the first column A5:A9) and returns its corresponding value (16) in the second column (as we mentioned 2 as column index in the formula) of the lookup range i.e; B5:B9.
Example 2:
=VLOOKUP(4,A5:C9,3,FALSE)
The VLOOKUP formula Looks for 4 (lookup_vlue) in the left most column of the lookup range (the first column A5:A9) and returns its corresponding value (64) in the third column (as we mentioned 3 as column index in the formula) of the lookup range i.e; C5:C9)
Example 3:
=VLOOKUP(6,A5:C9,3,FALSE)
The VLOOKUP formula Looks for 6 (lookup_vlue) in the left most column of the lookup range (the first column A5:A9) and should returns it corresponding value. But we 6 is not present in the lookup_vector, so it will return #NA as result (as it does not found any match in the table array).
Example 4:
=VLOOKUP(6,A5:C9,3,TRUE)
We use TRUE in very special cases. We should understand the use of TRUE, otherwise it may results the wrong values.
We can use TRUE if you you want to get the nearest lookup values if no match found. You have to use FALSE to get the exact lookup values.
The VLOOKUP formula Looks for 6 (lookup_vlue) in the left most column of the lookup range (the first column A5:A9) and should returns it corresponding value. But we 6 is not present in the lookup_vector, so it should return #NA as result. But we mentioned TRUE in the match type. It will return result value for nearest value of lookup value (i.e; 5 is the nearest to 6 and it return it’s correspondent value)
Vlookup Limitations, Rules and Known Issues
Here are the rules, limitations of the Vlookup and suggestions when you get #NA Error.
- Vlookup can look from Left to Right, and Right to Left is not possible and Vlookup does not work
- You can use Offset,Match,Index function to lookup the values from right to Left
- Vlookup will be keep returning #NA error if it is not found the lookup value in the table array
- You can replace #NA error with 0 using IFERROR formula (=IFERROR( VLOOKUP formula,0))
- You can use IF condition and ISNA formulas to print Yes or No as a result (=IF(ISNA(VLOOKUP formul)=TRUE,”Yes”,”No”))
- If the vlookup value for the lookup value is blank, na returns 0
- Vlookup is not case sensitive, you use Exact,Offset alternatively
- Vlookup is based on single lookup value and does not support multiple criteria
- You can get the vlookup value with multiple criteria by Concatenating the look up values
- You can use Offset, Match, Index function to find second match or last match value in the data array
Here is the reference for other Lookup Formulas
- LOOKUP: Returns the value in a row or column that matches a value in a row or column.
- HLOOKUP: Returns the value in a row that matches a value in the top row of a reference table.
- CHOOSE: Returns the value from a list of values, based on an index number.
- INDEX: Returns the value from a table, based on an index number.
- MATCH: Returns the position of a value in an array or list.
- OFFSET: Returns the cell value which is an offset from a given cell reference.
Vlookup Between Two Columns,Worksheets,Workbooks
The above examples explained how to use the Vlookup between two Columns in data array. Here are the Vlookup formulas to use in between two Worksheets and two workbooks.
You can use the below formula when you want to lookup the values between two worksheets:
=VLOOKUP(Sheet1!A1,Sheet2!A1:B3,2,FALSE)
or
=VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)
Here you can observe that the look up values is from Sheet1 and the lookup table array is in Sheet2.
You can use the below formula when you want to look up the values between two workbooks:
=VLOOKUP(A1,[Book2]Sheet1!$A$1:$B$3,2,0)
Or
=VLOOKUP([Book1]Sheet1!A1,[Book2]Sheet1!$A$1:$B$3,2,0)
Here you can observe that the look up values is from Work Book1 and the lookup table array is in Work Book2.
Vlookup using VBA
You can use the Vlookup function in the using Application.WorksheetFunctions. Here is the example vlookup vba code:
lookupValu=5 ' You can change this
set lookupRng=Sheet1.Range("A1:B10")
vLookupVal=Application.WorksheetFunction.VLookup(lookupValu, lookupRng, 2, 0)
Try to use Vlookup function with the combination of IFERROR formula to avoid the VBA Run time errors.
Reference:
Please refer the below article for more Lookup & Reference Excel functions.
Lookup & Reference Excel Formulas
Please refer the below article for more Excel Functions.
Excel Formulas | Home