How to Use VLOOKUP Function in Excel (2 Examples)

Overview of the VLOOKUP Function

Today I will be showing how you can use the VLOOKUP function of Excel.

VLOOKUP Function of Excel (Quick View)

Overview of the VLOOKUP Function

 

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

Syntax of VLOOKUP Function

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.

Non-Array VLOOKUP Function

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.

Array VLOOKUP Function

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.

VLOOKUP with Approximate 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.

A Data Set in Excel

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)

VLOOKUP for Maximum Value Holder

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)
[Array Formula. So press Ctrl + Shift + Enter unless you are in Office 365.]

VLOOKUP for Holders of n Top Values

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

VLOOKUP with Duplicates

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! Shows 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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo