How to Use VLOOKUP Function in Excel (2 Examples)

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:

Argument

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:

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:

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:

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:

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.

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:

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 here.
• 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.

1. VLOOKUP(372,B4:D20,2,0)
2. VLOOKUP(317,B4:D20,2,0)
3. VLOOKUP(309,,B4:D20,2,0)
4. VLOOKUP(293,B4:D20,2,0)
5. 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 the 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.

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

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.