VLOOKUP, XLOOKUP and INDEX MATCH: Find Data in Another Table

Imagine a price list with a hundred products, and a sales sheet where you type the product name and want the price to appear by itself. Or an employee list, and an attendance sheet where you type an ID and the name appears. This job, finding a value in a table by looking for a key, is called a lookup. It is among the most valuable Excel skills for office, accounts and data entry jobs, and it often appears in Excel tests.

Set up a price list

Add a new sheet called Prices with this table in A1:C6.

   A          B           C
1  Item       Category    Price
2  Notebook   Stationery  120
3  Pen pack   Stationery  80
4  Backpack   Bags        1400
5  Desk lamp  Home        650
6  Mouse      Office      700

In the Sales sheet we want the Price column (E) to be found from the item name in column B, so that the price is typed once, in one place, and never copied by hand. If a price changes, you change it in the price list only.

VLOOKUP

VLOOKUP is the classic lookup. The V stands for vertical, because it looks down a column.

=VLOOKUP(lookup_value, table_range, column_number, FALSE)

• lookup_value: what to find, such as the item name in B2.
• table_range: the table to search. The thing you search for must be in the first column of this range.
• column_number: which column of the table holds the answer, counting from 1 at the left of the range.
• FALSE: means exact match. Always use FALSE (or 0) when looking up names and codes.

In Sales!E2:

=VLOOKUP(B2, Prices!$A$2:$C$6, 3, FALSE)

Excel finds Notebook in the first column of the price table, then returns the value from the third column of the same row: 120. Copy it down. The dollar signs keep the table range fixed, as in lesson 3. Every row finds its own price, so the Amount column still works.

To find the category instead, change the column number to 2: =VLOOKUP(B2, Prices!$A$2:$C$6, 2, FALSE) gives Stationery for Notebook.

The #N/A error

#N/A means “not available”: the lookup value was not found. The cause is often a small difference, such as a trailing space, a spelling mistake, or a number stored as text in one table and as a number in the other. Check by comparing the two cells with =B2=Prices!A2. Use TRIM (next lesson) to remove spaces. If missing values are normal, wrap the lookup:

=IFERROR(VLOOKUP(B2, Prices!$A$2:$C$6, 3, FALSE), "Not in price list")

VLOOKUP's limits

• It can only look to the right. The search column must be the leftmost column of the table.
• The column number is typed by hand. If someone inserts a column in the table, your answer may change without warning.
• It returns only the first match.

These limits are why many people moved to XLOOKUP or INDEX MATCH.

XLOOKUP (Excel 2021 and Microsoft 365)

XLOOKUP fixes those problems and reads more naturally.

=XLOOKUP(lookup_value, lookup_range, return_range, "Not found")

=XLOOKUP(B2, Prices!$A$2:$A$6, Prices!$C$2:$C$6, "Not found")

It means: find B2 in the item column, and return the matching cell from the price column. It does exact matching by default, looks in any direction, and you can give a result for “not found” right inside it. XLOOKUP is not in Excel 2019 or older, and not in some other programs, so if your workplace uses an older Excel, use VLOOKUP or INDEX MATCH. Google Sheets also has XLOOKUP now, and LibreOffice has recently added it too, but check your version.

INDEX and MATCH: the all-version alternative

Two functions that work together in every version of Excel.

• MATCH(value, range, 0) finds the position of the value in a range. MATCH("Backpack", Prices!A2:A6, 0) returns 3.
• INDEX(range, position) returns the item at that position. INDEX(Prices!C2:C6, 3) returns 1400.

Put them together:

=INDEX(Prices!$C$2:$C$6, MATCH(B2, Prices!$A$2:$A$6, 0))

It reads: from the Price column, give me the item at the position where B2 is found in the Item column. It can look left or right, and it does not break when columns are inserted. Many experienced users prefer it.

Approximate match for bands

When you want to put a number into a band, such as a commission rate by sales amount or a grade by marks, approximate match is the right tool. Build a table sorted from smallest to largest, with the start of each band:

   A           B
1  Sales from  Commission
2  0           0%
3  1500        2%
4  2500        4%

=VLOOKUP(G2, $A$2:$B$4, 2, TRUE)

With TRUE, Excel finds the largest value that is less than or equal to G2. An amount of 2000 falls in the band that starts at 1500, so it returns 2%. An amount of 2800 returns 4%. The table must be sorted from small to large. This replaces long nested IF formulas with a table that anyone can read and change.

Lookups across sheets and files

A lookup table can sit on another sheet, as here, or in another file. When the table is in another workbook, Excel writes the file name into the formula, and the other workbook needs to be available when you open the file. For files you share, it is safer to keep the lookup table in the same workbook.

A checklist when a lookup fails

1. Is the lookup value in the first column of the range (VLOOKUP)?
2. Is the table range fixed with dollar signs and big enough to include every row?
3. Are there extra spaces, or different spelling or capitals? (Capitals do not matter, spaces do.)
4. Are the numbers stored as text in one of the tables?
5. Did you use FALSE for exact match?

Your task

1. Create the Prices sheet and replace the typed prices in Sales column E with a lookup formula. Choose VLOOKUP, XLOOKUP or INDEX MATCH.
2. Add a Category column to Sales using the same price table.
3. Type a misspelled item such as Notebok in B2 and see the error. Then wrap the lookup with IFERROR. Fix the spelling afterwards.
4. Create a commission band table and use an approximate match to find the commission rate for each amount.

How to check it worked

• Amount in G2 is still 1200 after you replaced the prices with the lookup.
• Backpack rows show a price of 1400 and category Bags.
• You can explain why FALSE is used for exact match.