Today I will be showing how you can use the VLOOKUP function of Excel.
VLOOKUP Function of Excel (Quick View)
Download Practice Workbook
Excel VLOOKUP Function (Syntax & Argument)
Summary
The VLOOKUP function looks for a given value in the leftmost column of a given table, and then returns a value in the same row from a specified column.
It is available from Excel 2003.
Syntax
The Syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Argument
Argument | Required or Optional | Value |
lookup_value | Required | The value which it looks for in the leftmost column of the given table. Can be a single value or an array of values. |
table_array | Required | The table in which it looks for the lookup_value in the leftmost column. |
col_index_num | Required | The number of the column in the table from which a value is to be returned. |
[range_lookup] |
Optional |
Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. Default is 1 (partial match). |
Note:
- The lookup_value can be a single value or an array of values. If you enter an array of values, the function will look for each of the values in the leftmost column and return the same row’s values from the specified column.
- The function will look for an approximate match if the [range_lookup] argument is set to 1. In that case, it will always look for the lower nearest value of the lookup_value, not the upper nearest one.
- If the col_index_number is a fraction in place of an integer, Excel itself will convert it into the lower integer. But it will raise #VALUE! error if the col_index_number is zero or negative.
Return Value
Returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the lookup_value.
Explanation
1. Non-Array Formula
When the lookup_value is a single value, it searches for the value in the leftmost column of the given table_array.
If it finds one, then it moves to the specified number of columns right given as col_index_num in the same row.
After moving to the specified number of columns right, it returns the value from the destination cell.
In the following figure, the formula is:
=VLOOKUP("Shane Lee",B5:D9,3,0)
It searches for “Shane Lee” in the leftmost column B of the table_array B5:D9.
Then it finds one in cell B7. Then it moves to column 3 (col_index_num) of the table, in the same row. That is cell D7.
And then it returns the value from that cell, in this case, it is the salary of Shane Lee, $22000.00
2. Array Formula
Again, when the lookup_value is an array of values in place of a single value, the function searches for each of the lookup_values in the leftmost column of the table_array one by one.
In the following figure, the formula is:
=VLOOKUP(F5:F6,B5:D9,3,0)
It first searches for F5 (Shane Lee) in the table and returns his salary, $22000.
Then it searches for F6 (Alfred Moyes) in the table and returns his salary, $22000.
Note: You have to press Ctrl + Shift + Enter to enter an Array Formula unless you are in Office 365.
3. In Case of Approximate Match
In the case of an approximate match (when the [range_lookup] argument is zero), the function always looks for the lowest nearest match.
In the following figure, the formula is:
=VLOOKUP(168,B4:D8,2,1)
Here, the [range_lookup] argument is 1, so it searches for an approximate match.
The lookup_value is 168.
It settles in 135, the lower nearest value to 168, and returns the corresponding Employee Name, Natalia Austin.
But the upper nearest value 169 is far closer, still, it does not go there.
Excel VLOOKUP Function: 2 Examples
1. Finding out the Holder of Maximum Value from a Data Set
Let us have a look at this data set.
We have the employee IDs, employee names, and their salaries of a company named Saturn Group in columns B, C, and D respectively.
Now we shall try to find out the holder of the maximum ID using the VLOOKUP function.
The formula will be:
=VLOOKUP(MAX(B4:B20),B4:D20,2,0)
See, we have found the Employee with the maximum ID, Angela Mills with an ID of 372.
Explanation of the Formula
- MAX(B4:B20) returns the maximum value between B4 to B20 (Employee IDs). In this case, it is 372. So the formula becomes: VLOOKUP(372,B4:D20,2,0)
- Then it searches for an exact match of the lookup_value 372 in the leftmost column B of the table_array B4:D20. It finds one in cell B19.
- Finally, it moves to column 2 (col_index_num) of the same row, to cell C19. And returns what it gets there. Here it is Angela Mills, the employee with the maximum ID.
Formula | Output | Explanation |
=VLOOKUP(MAX(B4:B20),B4:D20,2,0) | Angela Mills | First determines the maximum ID in the range B4 to B20. It is 372. Then searches for it in the leftmost column B of the table_array B4:D20. After finding one, it moves the 2nd column of the same row of the table. There it finds the name “Angela Mills”. |
2. Finding out the Holders of Top n Values from a Data Set
We try to determine the holders of any top n values from a data set using the VLOOKUP function.
Let’s find out the employees with the top 5 IDs from the same data set.
The formula will be:
=VLOOKUP(LARGE(B4:B20,ROW(A1:A5)),B4:D20,2,0)
See, we have got the employees with the top 5 IDs.
Explanation of the Formula
- ROW(A1:A5) returns an array of numbers from 1 to 5, {1,2,3,4,5}. See ROW function.
- LARGE(B4:B20,ROW(A1:A5)) becomes LARGE(B4:B20,{1,2,3,4,5}). It then returns the top 5 IDs from the cells B4 to B20. These are: {372,317,309,293,267}.
- VLOOKUP(LARGE(B4:B20,ROW(A1:A5)),B4:D20,2,0) then becomes VLOOKUP({372,317,309,293,267},B4:D20,2,0). It is a combination of total 5 formulas.
- VLOOKUP(372,B4:D20,2,0)
- VLOOKUP(317,B4:D20,2,0)
- VLOOKUP(309,,B4:D20,2,0)
- VLOOKUP(293,B4:D20,2,0)
- VLOOKUP(267,B4:D20,2,0)
- VLOOKUP(372,B4:D20,2,0) searches for an exact match of 372 in the leftmost column B of table B4:D20. After finding one, it moves to the 2nd column of the same row of the table, then returns the name of the employee. In this case, Angela Mills.
- The rest of the formulas do the same. Thus we get the list of the employees with the top 5 salaries.
Formula | Output | Explanation |
=VLOOKUP(LARGE(B4:B20,ROW(A1:A5)),B4:D20,2,0) | Angela Mills
Ricky Ben Mathew Rilee Usman Malik Benjamin Stokes |
Searches for an exact match of each of the top 5 IDs in the leftmost column B of the table B4:D20, then moves to column 2 and returns the names with the top 5 maximum IDs. |
Limitations of Excel VLOOKUP Function
- The first limitation of the VLOOKUP function is that you can not use it when the lookup_value is in a column right to the required value.
For example, in example 1, you can not use the VLOOKUP function if you are asked to find out the employee with the maximum salary. Because the salary is in a column right to the required value, employee name.
You can use the XLOOKUP or INDEX-MATCH function of Excel to come out of this limitation.
- If you have the lookup_value more than once, the VLOOKUP function will only provide you with information about the first one it gets.
For example, in the data set of example 1, there are two employees named Mathew Rilee. Now if we want to get the salary of Mathew Rilee, we will only get the salary of the first one, $28000.
You can solve this problem using the FILTER function of Excel.
- In the case of an approximate match, the VLOOKUP function always settles for the lower nearest value of the lookup_value, even when the upper nearest value is closer (See the 3rd point of the Explanation Section).
- The VLOOKUP function does not update automatically when you insert a new column. To get rid of this problem, you can use the INDEX-MATCH function of Excel.
Common Errors with Excel VLOOKUP Function
Error | When They Show |
#N/A! | Shows when it does not find a match of the lookup_value in the leftmost column. |
#VALUE! | Show when an argument of the function is of the wrong data type. For example, when the col_index_number is negative, or a text or the [range_lookup] argument is not 0 or 1. |
Conclusion
In this way, you can use the VLOOKUP function of Excel to extract out a value or an array of values matching another value from any data set. Do you have any other questions? Feel free to inform us.
Further Readings
- How to Use SORT Function in Excel (4 Examples)
- How to use MATCH function in Excel (3 Examples)
- How to Use HLOOKUP Function in Excel (8 Suitable Approaches)
- VLOOKUP and HLOOKUP combined Excel formula (with example)
- Using Excel to Lookup Partial Text Match [2 Easy Ways]
- How to Find Duplicate Values in Excel using VLOOKUP