How to Use VLOOKUP Function in Excel (8 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


VLOOKUP Function of Excel (Quick View)

The following image is a quick view of Excel VLOOKUP.

Excel VLOOKUP Function Overview


Introduction to 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 in Excel 2003 and all later versions.

Syntax:

The Syntax of the VLOOKUP function is:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Arguments:

Argument Required/Optional Value
lookup_value Required  The value that it looks for is 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. The 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.

Available in:

Excel 365 | Excel 2021 | Excel 2021 for Mac | Excel 2019 | Excel 2019 for Mac | Excel 2016 | Excel 2016 for Mac | Excel 2013, 2010, 2007 | Excel for Mac 2011 | Excel Starter 2010


How to Use the VLOOKUP Function in Excel: 8 Suitable Examples

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",B6:D10,3,FALSE)

Use of VLOOKUP function in Excel for dummies

  • First, it searches for “Shane Lee” in the leftmost column B of the table_array B6:D10.
  • Then it finds one in cell B8. Then it moves to column 3 (col_index_num) of the table, in the same row. That is cell D8.
  • And then it returns the value from that cell, in this case, it is the salary of Shane Lee, $22000.00.

We have already seen the basic use of VLOOKUP. Now, in this section,  we will see 7 more handy examples, some of them used VLOOKUP alone, and some of them need a combination of VLOOKUP with other Excel functions.


1. Finding Out the Holder of Maximum Value from a Dataset

Let us have a look at this dataset first.

We have the employee IDs, employee names, and salaries of a company named Saturn Group in columns B, C, and D respectively.

Recorded data of some company 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(B5:B21),B5:D21,2,0)

Finding Out the Holder of Maximum Value from a Data Set Using VLOOKUP

  • See, we have found the Employee with the maximum ID, Angela Mills with an ID of 372.

🔎 Explanation of the Formula:

  • MAX(B5:B21) returns the maximum value between B4 to B20 (Employee IDs). In this case, it is 372. So the formula becomes: VLOOKUP(372,B5:D21,2,0)
  • Then it searches for an exact match of the lookup_value 372 in the leftmost column B of the table_array B5:D21. It finds one in cell B20.
  • Finally, it moves to column 2 (col_index_num) of the same row, to cell C20. 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 Dataset

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(B5:B21,ROW($A$1:$A$5)),B5:D21,2,0)

Finding Out the Holders of Top n Values from a Dataset

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}. For details, see this article.
  • LARGE(B5:B21,ROW(A1:A5)) becomes LARGE(B5:B21,{1,2,3,4,5}). It then returns the top 5 IDs from cells B5 to B21. These are: {372,317,309,293,267}.
  • VLOOKUP(LARGE(B5:B21,ROW(A1:A5)),B5:D21,2,0) then becomes VLOOKUP({372,317,309,293,267},B5:D21,2,0). It is a combination of a total of 5 formulas.

♦ VLOOKUP(372,B5:D21,2,0)
♦ VLOOKUP(317,B5:D21,2,0)
♦ VLOOKUP(309,B5:D21,2,0)
♦ VLOOKUP(293,B5:D21,2,0)
♦ VLOOKUP(267,B5:D21,2,0)

  • VLOOKUP(372,B5:D21,2,0) searches for an exact match of 372 in the leftmost column B of table B5:D21. 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.

Note:

It’s an array formula. So press Ctrl + Shift + Enter unless you are in Office 365.

Read More: 7 Practical Examples of VLOOKUP Function in Excel


3. Find an Approximate Match with Excel VLOOKUP Function

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(G4,B5:D9,2,1)

Find an Approximate Match with Excel VLOOKUP Function

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

Read More: How to Use VLOOKUP Function with Exact Match in Excel


4. Match Multiple Lookup Values with Excel VLOOKUP 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(G4:H4,B6:D10,3,FALSE)

Match Multiple Lookup Values with Excel VLOOKUP Array Formula

  • It first searches for G4 (Shane Lee) in the table and returns his salary, $22000.
  • Then it searches for H4 (Alfred Moyes) in the table and returns his salary, $22000.

Note:

Note that, you have to press Ctrl + Shift + Enter to enter an Array Formula unless you are in Office 365.


5. Combine the CHOOSE Function with Excel VLOOKUP to Match Multiple Conditions

The VLOOKUP function can be used to extract data for multiple criteria lookups. But alone it cannot do that. You can combine CHOOSE, IF, or MATCH functions to match multiple criteria with VLOOKUP. In this example, we will see a VLOOKUP-CHOOSE formula in this regard.

Let us get introduced to the dataset for this example.

Here, we have the Brand, Model, Chipset, RAM, and Price data of some mobile phone companies.

  • We set two criteria, Brand and Model, to get the corresponding Price in cell C18.
  • The formula will be:
=VLOOKUP($D$15&$D$16,CHOOSE({1,2},$B$5:$B$13&$C$5:$C$13,$F$5:$F$13),2,FALSE)

Combine CHOOSE Function with Excel VLOOKUP to Match Multiple Conditions

Read More: 10 Best Practices with VLOOKUP in Excel


6. Use a Helper Column and Merge VLOOKUP with the MATCH Function for Multiple Criteria

In this example, we will see an easier alternative to the previous formula to match multiple criteria with VLOOKUP. This time, we will combine the MATCH function with VLOOKUP, and before that, we will need to add a helper column to the leftmost position of the dataset.

  • In the following image, we can see the formation of a helper column where we have concatenated the Brand and Model columns with the following formula.
=C5&D5

create a helper column

  • Then apply the following formula in the output cell to get the desired result.
=VLOOKUP(D15&D16,B5:G13,MATCH(B18,B4:G4,0),FALSE)

Use a Helper Column and Merge VLOOKUP with MATCH Function for Multiple Criteria

Read More: INDEX MATCH vs VLOOKUP Function


7. Combine VLOOKUP with INDIRECT Function to Lookup Across Spreadsheets

In the last example, we will see how we can use the VLOOKUP function to look up values across multiple sheets. To do this, we will need the help of the INDIRECT function. Let’s first introduce the dataset.

We have 4 sheets named Jan, Feb, Mar, and Apr with the data for the months of January, February, March, and April. The following is a glimpse of them.

  • Now, we will create another sheet that contains the datasheet names. Note that, you have to write the names of the sheets accurately word by word if not you want to get errors.

  • Okay, now you will create a named range for the range B3:B6.

  • Create the named range and name it Lookup_Sheet_List.
  • If your name contains multiple words, you have to put underscores in between.

  • Now, go to your main sheet where you want to perform lookup operations. Here, we are looking up Order nos.
  • In cell C5, input the following formula.
=IFNA(VLOOKUP($B5, INDIRECT("'"&INDEX(Lookup_Sheet_List,MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_Sheet_List &"'!$B$3:$B$6"), $B5)>0), 0)) & "'!$B$5:$D$9"), 2, FALSE), "Not found")

Combine VLOOKUP with INDIRECT Function to Lookup Across Spreadsheets

  • Similarly, to get Amounts, input the following formula in the column beside.
=IFNA(VLOOKUP($B5, INDIRECT("'"&INDEX(Lookup_Sheet_List,MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_Sheet_List &"'!$B$3:$B$6"), $B5)>0), 0)) & "'!$B$5:$D$9"), 3, FALSE), "Not found")

  • Note that, you have to change the column index number accordingly.

For these formulas, credit goes to ablebits.


8. Using the VLOOKUP Function to Lookup to the Left

We know that VLOOKUP cannot look up values from the left-side columns of the input range. But wait, that’s not the last truth about VLOOKUP! You can combine the IF function with VLOOKUP to look up from the left columns of input ranges. In this part of this article, we will see how to do that.

  • This time, we want to look up the Employee’s Name and get the corresponding ID.
  • The formula will be:
=VLOOKUP(G4,IF({1,0},C5:C9,B5:B9),2,0)

How to Lookup to Left with Excel VLOOKUP Function

Here, {1,0} inside the IF function is important, if you alter the sequence, i.e. put {0,1} instead, the formula will not work as expected.

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples


Common Errors with Excel VLOOKUP Function

The VLOOKUP function has the following common errors.

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.

Limitations of Excel VLOOKUP Function

Though VLOOKUP is one of the most used functions in Excel, it has some limitations. Here are some of these-

  • The first limitation of the VLOOKUP function is that you can not use it when the lookup_value is in a column right next 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, the employee name.
    You can use the XLOOKUP or INDEX-MATCH functions of Excel to come out of this limitation.
  • Another limitation is- 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 returns only the first match, if there are multiple matches.

You can solve this problem using the FILTER function.

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

Download Practice Workbook

You can download the following practice workbook that we used to write this article. You can practice along with it while reading this write-up.


Conclusion

In this way, you can use the VLOOKUP function of Excel to extract a value or an array of values matching another value from any dataset in Excel. Do you have any other questions? Feel free to inform us.


Excel VLOOKUP Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. Thanks for your teaching in Excel. You gave me a lot of insight into the new features of Excel 365

    • Hello, Ruben!

      It’s glad to know that our content is helpful to you. To know more about Excel stay in touch with ExcelDemy.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo