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:

1st Example

VLOOKUP Example 1

2nd Example

VLOOKUP Example 2

3rd Example

VLOOKUP Example 3

4th Example

VLOOKUP Example 4

5th Example

VLOOKUP Example 5

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

A worksheet with columns that use VLOOKUP to get data from other tables
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

  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.
The Manage Relationships dialogue 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.

Common Problems

ProblemWhat went wrong
Wrong value returnedIf range_lookup 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.
#N/A in cellIf range_lookup is TRUE, then if the value in the lookup_value is smaller than the smallest value in the first column of the table_array, you’ll receive the #N/A error value.
If range_lookup is FALSE, the #N/A error value denotes that the exact number isn’t found.
For further information on resolving #N/A errors in VLOOKUP, see How to correct a #N/A error in the VLOOKUP function.
#REF! in cellIf col_index_num is greater than the number of columns in table-array, you’ll have the #REF! error value returned to you.
For more information on resolving #REF! errors in VLOOKUP, see How to correct a #REF! error.
#VALUE! in cellIf the table_array is less than 1, you’ll receive the #VALUE! error value.
For more information on resolving #VALUE! errors in VLOOKUP, see How to correct a #VALUE! error in the VLOOKUP function.
#NAME? in cellThe #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 cellThis 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 thisWhy
Use absolute references for range_lookupUsing 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 table_array isn’t stored as text values. Otherwise, VLOOKUP may return a wrong or unexpected value.
Sort the first columnSort the first column of the table_array before using VLOOKUP when range_lookup is TRUE.
Use wildcard charactersIf range_lookup is FALSE and lookup_value 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 Fontana 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)

Leave a Reply

%d bloggers like this: