# VLOOKUP function

• Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

Tip: Try using the new XLOOKUP function, an enhanced version of VLOOKUP that operates in any direction and captures precise matches automatically, simplifying its use and making it much more accessible compared to its previous incarnation.

Use the VLOOKUP function anytime you must track down specific data in a table or a range by row. For example, look up a price of an automotive part according to the part number, or find an employee name directly from their employee ID.

Tip: Check out these YouTube videos from Microsoft Creators for more help with VLOOKUP!

In its simplest form, the VLOOKUP function says:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

Tip: The secret to VLOOKUP is to organise your data so that the value you look up (Fruit) is to the left of the return value (Amount) you seek to find.

## Technical details

### How to get started

There are four segments of information that you require before you can even start to construct the VLOOKUP syntax:

1. The value you want to look up, also referred to as the lookup value.
2. The range where the lookup value is found. Remember that the lookup value should always be in the first column in the range for VLOOKUP to function correctly. For instance, if your lookup value is in cell C2 then your range should begin with C.
3. The column number in the range that includes the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
4. Optionally, you can confirm TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the standard value will always be TRUE or approximate match.

Now integrate all of the above together as shown below:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

## Examples

Here are some examples of VLOOKUP:

### 2nd Example ### 3rd Example ### 4th Example ### 5th Example ### Combine data from several tables onto one worksheet by using VLOOKUP

You can use VLOOKUP to merge several tables into one, provided one of the tables has similar fields which are situated in the remaining tables. This can be particularly valuable if you must share a workbook with people who have older Excel versions which don’t support data features with numerous tables as data sources – by linking the sources into one table and altering the data feature’s data source to the new table, the data feature can be applied in older Excel versions (given the data feature itself is supported by the older version).

### Step-by-step guide

1. Copy the table with the frequent fields onto a new worksheet, and call it a suitable name.
2. Select Data > Data Tools > Relationships to launch the Manage Relationships dialog box.
1. For each displayed relationship, note the following:
• The field that connects the tables (listed in parentheses in the dialogue box). This is the lookup_value for your VLOOKUP formula.
• The Related Lookup Table name. This is the table_array in your VLOOKUP formula.
• The field (column) in the Related Lookup Table that contains the sought data you want in your new column. This information is not listed in the Manage Relationships dialogue – you’ll have to check the Related Lookup Table to observe which field you want to retrieve. You want to remember the column number (A=1) – this is the col_index_num in your formula.
2. To create a field to the new table, type your VLOOKUP formula in the first empty column using the information you collected in step 3. In our example, column G uses Attorney (the lookup_value) to get the Bill Rate data from the fourth column (col_index_num = 4) from the Attorneys worksheet table, tblAttorneys (the table_array), with the formula =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE). The formula could even use a cell reference and a range reference. In our example, it would be =VLOOKUP(A2,’Attorneys’!A:D,4,FALSE).
3. Keep adding fields until you have all your essential fields. If you are endeavouring to prepare a workbook including data features that use several tables, modify the data source of the data feature to the new table.

## Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.