*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:

- The value you want to look up, also referred to as the lookup value.
- 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.
- 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.
- 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:

### 1st Example

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

Here, columns A-F and H contain values or formulas that solely use values on the worksheet, and the remaining columns use VLOOKUP and the values of column A (Client Code) and column B (Attorney) to collect data from other tables. |

### Step-by-step guide

- Copy the table with the frequent fields onto a new worksheet, and call it a suitable name.
- Select
**Data**>**Data Tools**>**Relationships**to launch the Manage Relationships dialog box.

- For each displayed relationship, note the following:
- The field that connects the tables (listed in parentheses in the dialogue box). This is the
for your VLOOKUP formula.**lookup_value** - The Related Lookup Table name. This is the
in your VLOOKUP formula.*table_array* - 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
in your formula.*col_index_num*

- The field that connects the tables (listed in parentheses in the dialogue box). This is the
- 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
) to get the Bill Rate data from the fourth column (*lookup_value*= 4) from the Attorneys worksheet table, tblAttorneys (the*col_index_num*), with the formula*table_array***=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**). - 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.

### Common Problems

Problem | What went wrong |
---|---|

Wrong value returned | If is TRUE or left out, the first column has to be sorted alphabetically or numerically. If the first column is unarranged, the return value may be a wildly inaccurate value. Either sort the first column, or use FALSE for an exact match.range_lookup |

#N/A in cell | If is TRUE, then if the value in the range_lookup is smaller than the smallest value in the first column of the lookup_value, you’ll receive the #N/A error value. table_arrayIf is FALSE, the #N/A error value denotes that the exact number isn’t found.range_lookupFor further information on resolving #N/A errors in VLOOKUP, see How to correct a #N/A error in the VLOOKUP function. |

#REF! in cell | If is greater than the number of columns in col_index_num, you’ll have the #REF! error value returned to you.table-arrayFor more information on resolving #REF! errors in VLOOKUP, see How to correct a #REF! error. |

#VALUE! in cell | If the is less than 1, you’ll receive the #VALUE! error value.table_arrayFor more information on resolving #VALUE! errors in VLOOKUP, see How to correct a #VALUE! error in the VLOOKUP function. |

#NAME? in cell | The #NAME? error value typically indicates that the formula is missing quotes. To look up a person’s name, ensure you use quotes around the name in the formula. For example, input the name as “Fontana” in =VLOOKUP(“Fontana”,B2:E7,2,FALSE).For further information, see How to correct a #NAME! error. |

#SPILL! in cell | This specific #SPILL! error normally indicates that your formula is depending on implicit intersection for the lookup value, and using a whole column as a reference. For example, =VLOOKUP(A:A,A:C,2,FALSE). You can repair the issue by anchoring the lookup reference with the @ operator such as this: =VLOOKUP(@A:A,A:C,2,FALSE). Alternatively, you can use the traditional VLOOKUP method and refer to a single cell rather than a full column: =VLOOKUP(A2,A:C,2,FALSE). |

## Best practices

Do this | Why |
---|---|

Use absolute references for range_lookup | Using absolute references enables you to fill-down a formula so that it always inspects the same exact lookup range. Learn how to use absolute cell references. |

Don’t store number or date values as text. | In the process of searching number or date values, make sure the data in the first column of isn’t stored as text values. Otherwise, VLOOKUP may return a wrong or unexpected value.table_array |

Sort the first column | Sort the first column of the before using VLOOKUP when table_array is TRUE.range_lookup |

Use wildcard characters | If is FALSE and range_lookup is text, you can use the wildcard characters—the question mark (?) and asterisk (*)—in lookup_value. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to locate an actual question mark or asterisk, enter a tilde (~) in front of the character.For example, =VLOOKUP(“Fontan?”,B2:E7,2,FALSE) will search for every result of lookup_valueFontana with a last letter that could differ. |

Make sure your data doesn’t contain erroneous characters. | Whilst searching text values in the first column, check the data in the first column has no leading spaces, trailing spaces, alternating use of straight ( ‘ or ” ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these scenarios, VLOOKUP may capture an unexpected value. To get accurate results, try using the CLEAN function or the TRIM function to delete trailing spaces after table values in a cell. |

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

## See Also

Quick Reference Card: VLOOKUP refresher

Quick Reference Card: VLOOKUP troubleshooting tips

YouTube: VLOOKUP videos from Microsoft Creators

How to correct a #VALUE! error in the VLOOKUP function

How to correct a #N/A error in the VLOOKUP function

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Excel functions (alphabetical)

Excel functions (by category)

VLOOKUP (free preview)