VLOOKUP function in Excel (2024)

Exact Match | Approximate Match | Vlookup Looks Right | First Match | Partial Match | Vlookup is Case-insensitive | Multiple Criteria | #N/A error | Multiple Lookup Tables | Index and Match | Table Magic | Xlookup

The VLOOKUP function is one of the most popular functions in Excel. This page contains many easy to follow VLOOKUP examples.

Exact Match

Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. Let's take a look at the arguments of the VLOOKUP function.

1. The VLOOKUP function below looks up the value 53 (first argument) in the leftmost column of the red table (second argument).

VLOOKUP function in Excel (1)

2. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

VLOOKUP function in Excel (2)

Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error.

3. Here's another example. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79.

VLOOKUP function in Excel (3)

Approximate Match

Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

1. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). There's just one problem. There's no value 85 in the first column.

VLOOKUP function in Excel (4)

2. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. In this example, this will be the value 80.

VLOOKUP function in Excel (5)

3. The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table.

VLOOKUP function in Excel (6)

Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

Vlookup Looks Right

The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right.

1. For example, the VLOOKUP function below looks up the first name and returns the last name.

VLOOKUP function in Excel (7)

2. If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary.

VLOOKUP function in Excel (8)

Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID. The VLOOKUP function only looks to the right. No worries, you can use INDEX and MATCH in Excel to perform a left lookup.

First Match

If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. For example, take a look at the VLOOKUP function below.

VLOOKUP function in Excel (9)

Explanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed.

Partial Match

The VLOOKUP function supports wildcards for partial matches. For example, take a look at the VLOOKUP function below.

VLOOKUP function in Excel (10)

Explanation: an asterisk (*) matches zero or more characters. The VLOOKUP function matches the first instance that begins with "Jess". Use "*"&G2 for an "ends with" match. Use "*"&G2&"*" for a "contains" match.

Vlookup is Case-insensitive

The VLOOKUP function in Excel performs a case-insensitive lookup. For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.

VLOOKUP function in Excel (11)

Explanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup.

Multiple Criteria

Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup.

VLOOKUP function in Excel (12)

Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson.

#N/A error

If the VLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the VLOOKUP function below cannot find the value 28 in the leftmost column.

VLOOKUP function in Excel (13)

2. If you like, you can use the IFNA function to replace the #N/A error with a friendly message.

VLOOKUP function in Excel (14)

Note: the IFNA function was introduced in Excel 2013. If you're using Excel 2010 or Excel 2007, combine IF and ISNA or use IFERROR.

Multiple Lookup Tables

When using the VLOOKUP function in Excel, you can have multiple lookup tables. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE.

1. Create two named ranges: Table1 and Table2.

VLOOKUP function in Excel (15)

2. Select cell E4 and enter the VLOOKUP function shown below.

VLOOKUP function in Excel (16)

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The second argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. Set the fourth argument of the VLOOKUP function to TRUE to return an approximate match.

3. Press Enter.

4. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10.

VLOOKUP function in Excel (17)

Note: for example, Walker receives a bonus of $1,500. Because we're using named ranges, we can easily copy this VLOOKUP function to the other cells without worrying about cell references.

Index and Match

Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer.

VLOOKUP function in Excel (18)

Table Magic

Tables can make VLOOKUP formulas a lot easier. In the example below, a single VLOOKUP function automatically looks up all country codes.

VLOOKUP function in Excel (19)

Note: visit our page about merging tables to learn how to create this VLOOKUP function and bring your Excel game to a new level.

Xlookup

If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

VLOOKUP function in Excel (20)

VLOOKUP function in Excel (2024)

FAQs

How to do a VLOOKUP with multiple answers? ›

How to use VLOOKUP for multiple values
  1. Create a specific helper column on the table's left. ...
  2. Type your starting formula in the specific cell. ...
  3. Add the multiple search values. ...
  4. Input the table array. ...
  5. Pick a range lookup option.
Apr 8, 2024

Why is VLOOKUP not picking up all values? ›

Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.

How do you answer what is VLOOKUP in Excel? ›

VLOOKUP is a powerful function in Excel that searches for a value in the first column of a range (table or array) and returns a corresponding value in the same row from a specified column. The "V" in VLOOKUP stands for "Vertical," indicating that it searches vertically (downward) in a table.

Why is my VLOOKUP giving me the wrong answer? ›

VLOOKUP Returns a value but it is incorrect

There are a number of possible reasons for this: You didn't specify whether Excel must do an Exact or Approximate match (true or false as the last criteria) You specified that Excel does an approximate match but you should have required an exact match.

How to VLOOKUP with 2 criteria? ›

How to VLOOKUP for two values
  1. Step1: Create a separate column where we will create unique lookup_values by merging our two lookup criteria – name and country – for example “MellaThailand“, “MellaNigeria“, etc.
  2. Step2: Look up the range using the unique strings as the lookup_value to return a matching value.
Jan 5, 2024

Can I use VLOOKUP to return multiple values? ›

An immediate solution that comes to mind is using the Excel VLOOKUP function, but the problem is that it can only return a single match. Vlookup for multiple values can be done via a combined use of several functions.

What are the three rules for VLOOKUP? ›

The VLOOKUP function consists of three required arguments, in the following order: lookup value, table array, and column index number. The lookup value is the value for which you want to find matching data and must appear in the first column of the lookup table; it can be a value, a text string, or a cell reference.

What is a simple example for VLOOKUP in Excel? ›

Use the VLOOKUP function to look up a value in a table. For example: =VLOOKUP(A2,A10:C20,2,TRUE) =VLOOKUP("Fontana",B2:E7,2,FALSE)

What to use instead of VLOOKUP? ›

INDEX and MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right, which is all VLOOKUP can do.

What is the problem with VLOOKUP? ›

The lookup column is not the leftmost column of the table array. One of the most significant limitations of Excel VLOOKUP is that it cannot look to its left. Consequently, a lookup column should always be the leftmost column in the table array. In practice, we often forget about this and end up with #N/A errors.

How to do VLOOKUP between two sheets? ›

How to do VLOOKUP in Excel with two spreadsheets
  1. Click cell E2 of Sheet 1.
  2. Enter =VLOOKUP(B2,Sheet2!$A$2:$C$10,3,FALSE) . Here's a breakdown of the modified table array: Sheet2!: This is the name of the spreadsheet that contains the given cell range. ...
  3. Press Enter or Return.
Nov 6, 2023

How do I do a VLOOKUP with multiple column results? ›

How to Vlookup and return multiple values in Excel
  • Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
  • Select several adjacent cells in a single column (F1:F11 in the screenshot below), type the formula and press Ctrl + Shift + Enter to complete it.
Mar 22, 2023

How to VLOOKUP sum of multiple values? ›

VLOOKUP with SUM helps us calculate the sum of the numeric values based on the matching criterias or conditions. To perform VLOOKUP with SUM, we combine two built-in Excel functions, i.e., SUM() and VLOOKUP(), to get the following syntax, =SUM(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

How to use 2 vlookups in 1 formula? ›

All you have to do is nest the first VLOOKUP formula inside the input of the second VLOOKUP formula. Here, the inner VLOOKUP looks for the book's name and returns the ID. The outer VLOOKUP looks for the book's ID and returns its price from Table 2. That's all folks!

How do I use multiple IF and VLOOKUP in Excel? ›

For the formula, you'd need two sets of parentheses, one for the IF function, and one for the VLOOKUP function. Each argument within the functions is separated by commas.

References

Top Articles
Latest Posts
Article information

Author: Arielle Torp

Last Updated:

Views: 6147

Rating: 4 / 5 (61 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Arielle Torp

Birthday: 1997-09-20

Address: 87313 Erdman Vista, North Dustinborough, WA 37563

Phone: +97216742823598

Job: Central Technology Officer

Hobby: Taekwondo, Macrame, Foreign language learning, Kite flying, Cooking, Skiing, Computer programming

Introduction: My name is Arielle Torp, I am a comfortable, kind, zealous, lovely, jolly, colorful, adventurous person who loves writing and wants to share my knowledge and understanding with you.