XLOOKUP vs VLOOKUP vs INDEX/MATCH: Which Lookup Should You Use?

The short answer

If everyone who opens the file uses Excel 2021 or later, Microsoft 365, Excel for the web or Google Sheets, XLOOKUP is usually the best default. It matches exactly unless told otherwise, can return a column on either side of the lookup column, handles missing values with its own argument and is not broken by inserted columns. INDEX/MATCH does the same job in Excel 2019 and earlier. VLOOKUP works everywhere, but its defaults make it the easiest of the three to get silently wrong.

The examples in this guide use a fictional tea wholesaler. Its Orders sheet holds a customer ID in column B, and its Customers sheet lists IDs in column A, names in column B and segments in column C, in rows 2 to 500. The task is to bring each order’s customer segment into the Orders sheet.

VLOOKUP and its two traps

The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). It searches the first column of table_array and returns the value from the column number you give, so it can only look to the right. The first trap is the default: if you omit range_lookup, it is treated as TRUE, meaning approximate match, which expects the first column to be sorted ascending. On unsorted data it can return a wrong value with no error. Always write FALSE for an exact match. Google Sheets calls this argument is_sorted and uses the same default.

The second trap is the column number. The 3 in the example is a fixed count, not a reference, so it does not update when the table changes. Insert a column inside the range and the formula quietly returns whatever now sits in the third column. Nothing turns red; the numbers are simply wrong. Moving the return column, or copying the formula sideways with a hand-edited index, causes the same problem.

INDEX/MATCH: the compatible alternative

INDEX/MATCH splits the lookup in two. MATCH(lookup_value, lookup_array, 0) finds the position of the value, and INDEX(return_array, position) returns whatever sits at that position in another range. Because the return range is referenced directly, inserting columns does not break it, and the return column can sit on either side of the lookup column. It works in every version of Excel still in common use and in Google Sheets.

The 0 matters. MATCH’s match_type defaults to 1, which assumes the lookup range is sorted ascending and returns the position of the largest value less than or equal to the lookup value: the same trap as VLOOKUP’s default. Also make sure both ranges start and end on the same rows. If one starts at row 1 and the other at row 2, every result is shifted by one row.

XLOOKUP: arguments and defaults

The full syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Only the first three arguments are required, and the default is an exact match. The lookup and return arrays are separate ranges, so column order and inserted columns do not matter. They must be the same height, or the formula returns an error, and should start on the same row so results line up. A return_array several columns wide returns several values at once, spilling into the neighbouring cells.

match_mode sets how values are compared: 0 for exact (the default), -1 for exact or the next smaller item, 1 for exact or the next larger item, and 2 for wildcard matching with * and ?. search_mode sets the direction: 1 searches first to last (the default), -1 searches last to first, and 2 and -2 run a binary search that requires data sorted ascending or descending respectively. On unsorted data, binary search can return wrong results, so use it only when you control the sort order.

When approximate match is what you want

Approximate match is the right tool for bands and tiers, such as discounts, commission rates or shipping charges, where you want the threshold at or below a value rather than an exact hit. Use it deliberately, with the mode written out rather than left to a default. Keep the thresholds sorted ascending: VLOOKUP’s TRUE mode and MATCH’s match_type 1 depend on it, and a sorted table also makes any of the three results easy to check by eye.

Rules all three share

All three ignore case, so “ab-101” and “AB-101” count as the same key. When case matters, as with some product codes, compare with EXACT instead: =XLOOKUP(TRUE, EXACT(Customers!$A$2:$A$500, B2), Customers!$C$2:$C$500, "Not found"). In Google Sheets, wrap the EXACT part in ARRAYFORMULA. In Excel 2019 and earlier, use =INDEX(Customers!$C$2:$C$500, MATCH(TRUE, EXACT(Customers!$A$2:$A$500, B2), 0)) and confirm it with Ctrl+Shift+Enter.

All three also return the first match, searching from the top, and give no warning that duplicates exist. XLOOKUP can return the last match instead with search_mode -1, which helps when the newest record sits at the bottom. Before relying on a lookup, check the lookup table for duplicate keys, for example with =COUNTIF($A$2:$A$500, A2)>1 in a helper column on the Customers sheet, and decide which record should win.

When nothing matches, VLOOKUP and INDEX/MATCH return #N/A. Wrap them in IFNA rather than IFERROR, as in =IFNA(VLOOKUP(B2, Customers!$A$2:$C$500, 3, FALSE), "Not found"), because IFERROR also hides genuine mistakes such as a broken reference. If many rows come back not found, suspect the keys: trailing spaces, non-breaking spaces from web exports, or IDs stored as text on one side and as numbers on the other. Clean both sides with TRIM, SUBSTITUTE for CHAR(160), and VALUE or TEXT before looking up.

Speed, compatibility and choosing

Speed differences between the three are rarely the main reason a workbook is slow, and published comparisons vary with version, data size and how formulas are written. Common culprits are the same lookup repeated across many columns, very large ranges and volatile functions that recalculate constantly. Returning several columns from one XLOOKUP, or reusing one MATCH result across several INDEX formulas, can reduce repeated work. Binary search may help on large sorted data, at the cost of wrong answers if the sort order changes. Test any change on a copy.

Choose by compatibility first. If anyone opening the file uses Excel 2019 or earlier, XLOOKUP formulas will show #NAME? errors when their copy recalculates, so use INDEX/MATCH. Otherwise, standardise on XLOOKUP and keep VLOOKUP for quick checks, always with FALSE. The Practical Data Analysis with Spreadsheets & AI course puts lookups inside a wider workflow: cleaning keys, checking match rates and row counts after a join, and summarising the result with pivot tables.