XLOOKUP, INDEX and MATCH: the lookups worth learning

Lookups are the formulas that pull a value from one table into another: the price for a product code, the region for a customer, the cost for an order line. If you work with product feeds, sales exports or any data spread across more than one sheet, you’ll write them every week.

For years VLOOKUP was the default. It still works, but it has habits that cause quiet errors. This guide explains those limits, then covers XLOOKUP and the INDEX and MATCH pair, using one small product table throughout.

The example table (illustration only)

Every formula below uses this invented product list, in cells A1:D6 of a sheet, with headers in row 1.

Assume the SKU you want to look up is typed in cell G2, and a product name is typed in G3.

Where VLOOKUP falls short

A VLOOKUP for the price of the SKU in G2 looks like this:

=VLOOKUP(G2, A2:D6, 4, FALSE)

It has four weak spots.

• It only looks to the right. Microsoft’s documentation says the first column of the range must contain the value you are looking up. To find a SKU from a product name, VLOOKUP can’t help, because the SKU column sits to the left of the product column.
• Approximate match is the default. If you leave out the last argument, VLOOKUP uses approximate match, which expects the first column to be sorted. On an unsorted list of codes that can return the wrong row without any error. Always type FALSE for an exact match.
• The column number is hard-coded. The 4 means the fourth column of the range. Insert a new column inside the range and the formula still returns the fourth column, which now holds a different field.
• Missing values return #N/A, so you need IFNA or IFERROR around the formula to show a friendlier message.

XLOOKUP: syntax and the three arguments you need

XLOOKUP replaces the column number with a direct reference to the column you want back. The full syntax, from Microsoft’s XLOOKUP page, is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Only the first three are required. The price for the SKU in G2 becomes:

=XLOOKUP(G2, A2:A6, D2:D6)

Read it as: find G2 in the SKU column and return the matching entry from the price column. Because the return column is its own reference, inserting columns elsewhere doesn’t break it, and it can look left as easily as right. To find the SKU for the product name in G3:

=XLOOKUP(G3, B2:B6, A2:A6)

XLOOKUP uses exact match unless you tell it otherwise, which is the reverse of VLOOKUP. That one change removes the most common VLOOKUP mistake.

The optional arguments worth knowing

if_not_found

Without it, a missing value returns #N/A. With it, you choose the message:

=XLOOKUP(G2, A2:A6, D2:D6, "Not in list")

match_mode

This controls how a match is found:

• 0: exact match, returning #N/A if none is found. This is the default.
• -1: exact match, or the next smaller item if none is found.
• 1: exact match, or the next larger item if none is found.
• 2: wildcard match, where *, ? and ~ have special meanings.

Mode -1 suits banded tables, such as finding the delivery charge for an order value from a list of thresholds. Mode 2 finds the first product containing a word. On the example table, =XLOOKUP("*scarf*", B2:B6, D2:D6, "Not found", 2) returns 18.

search_mode

• 1: search from the first item. This is the default.
• -1: search from the last item upwards.
• 2 and -2: binary search on data sorted in ascending or descending order. Microsoft warns that unsorted data gives invalid results in these modes.

Reverse search has a practical everyday use. Suppose a price history sheet logs every price change in date order, with SKUs in column A and prices in column D. =XLOOKUP(G2, A2:A500, D2:D500, "No price", 0, -1) returns the most recent price for that SKU rather than the first one ever recorded.

Which versions have XLOOKUP

Microsoft’s support page lists XLOOKUP for Excel for Microsoft 365 on Windows and Mac, Excel 2021 and Excel 2024 on Windows and Mac, and the Excel apps for iPad, iPhone and Android. The same page states that XLOOKUP is not available in Excel 2016 or Excel 2019. If colleagues or clients use those versions, check the page for current details and use INDEX and MATCH in files you share with them.

Google Sheets also has an XLOOKUP function, with exact match and first-to-last search as its defaults. Its arguments have different names (search_key, lookup_range, result_range and missing_value) but behave the same way.

INDEX and MATCH: the version that works everywhere

Before XLOOKUP, the standard fix for VLOOKUP’s limits was to combine two functions. MATCH finds the position of a value in a list. INDEX returns the item at a given position. Together they do the same job:

=INDEX(D2:D6, MATCH(G2, A2:A6, 0))

If G2 holds SKU-104, MATCH(G2, A2:A6, 0) returns 4, its position in the list. INDEX(D2:D6, 4) then returns the fourth price, 35.

The 0 in MATCH matters. It asks for an exact match. Microsoft’s documentation gives the default match type as 1, which finds the largest value less than or equal to the lookup value and needs the list sorted in ascending order. Leave the 0 out on unsorted codes and you get the same quiet errors as an approximate VLOOKUP.

INDEX and MATCH looks left without any trouble. The SKU for the product name in G3:

=INDEX(A2:A6, MATCH(G3, B2:B6, 0))

To show a message instead of #N/A, wrap it: =IFNA(INDEX(D2:D6, MATCH(G2, A2:A6, 0)), "Not in list").

The same formulas work in Google Sheets. Its help page for INDEX describes pairing it with MATCH for lookups where the value you want can sit on either side of the lookup column.

Which one to use

• Use XLOOKUP when everyone who opens the file has Microsoft 365, Excel 2021 or later, or Google Sheets. It is shorter and its defaults are safer.
• Use INDEX and MATCH when files go to people on Excel 2016 or 2019, or when you are editing older workbooks that already use it.
• Keep VLOOKUP for reading other people’s spreadsheets. If you do write one, always set the last argument to FALSE.

Whichever you choose, a few habits prevent most lookup errors. Check lookup columns for leading and trailing spaces. Make sure codes stored as numbers in one table aren’t stored as text in the other. Lock ranges with $ signs, such as $A$2:$A$6, before copying a formula down a column.