XLOOKUP vs VLOOKUP: Which Should You Use?

Use XLOOKUP if everyone who opens the file has Microsoft 365, Excel 2021 or Excel 2024. Use VLOOKUP (or INDEX/MATCH) if anyone is on Excel 2016 or 2019. When choosing between XLOOKUP and VLOOKUP, version support usually decides it before features do.

Here is the same lookup written both ways. It finds the ID in F2 and returns its price:

=XLOOKUP(F2,A2:A6,C2:C6,"Not found")

=VLOOKUP(F2,A2:C6,3,FALSE)

Microsoft states that XLOOKUP is not available in Excel 2016 and 2019. Some sites say 2019 has it. That's wrong, and it's a common reason a shared file shows #NAME? on someone's screen. Details are in the XLOOKUP function page from Microsoft Support.

Same lookup, both formulas: a small example

Say you have a short product list in A1:C6, with headers in row 1. Prices are in USD.

Type the ID you want in F2. These are the results you should see:

Excel may show 24.00 and 29.50 as 24 and 29.5 unless the cells are formatted as currency or two decimals. The value is the same, so your result isn't wrong.

Compare the two columns for each ID: they match for IDs that exist. The difference shows up when the ID is missing, when the layout changes, or when you forget an argument.

How each formula works, argument by argument

A few terms first. The lookup column is the column that holds the value you search for (the IDs here). An exact match means the value must be identical. An approximate match means Excel finds the closest value without going over, and it only behaves sensibly when the list is sorted.

XLOOKUP arguments

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

• lookup_value: what you're searching for, such as F2.
• lookup_array: the one column or row to search, such as A2:A6.
• return_array: the column or row to pull the answer from, such as C2:C6.
• if_not_found (optional): text or a value to show when nothing matches.
• match_mode (optional): defaults to exact match. Other settings allow nearest-value and wildcard matching.
• search_mode (optional): defaults to searching first to last. Use -1 to search last to first.

VLOOKUP arguments

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: what you're searching for.
• table_array: the block of cells to search. The lookup column must be its first (leftmost) column.
• col_index_num: which column of that block to return, counted from the left. In A2:C6, the price is column 3.
• range_lookup (optional): TRUE or omitted means approximate match. FALSE means exact match.

Microsoft documents the same behavior on its VLOOKUP function page.

Where XLOOKUP is better

Exact match is the default

Leave off the last argument in VLOOKUP and Excel does an approximate match. With sorted IDs and an ID that doesn't exist, that can return a confident wrong answer.

Try =VLOOKUP(1009,A2:C6,3) on the table above. There's no 1009, but Excel settles on the closest ID below it, 1005, and returns 29.50. No error appears. The sheet just shows a wrong price.

=XLOOKUP(1009,A2:A6,C2:C6) returns #N/A instead, which is a signal you can see and fix. Add "Not found" as the fourth argument and you get a readable message.

No column number to break

VLOOKUP counts columns with a typed number. Insert a new column between Name and Price, and the table becomes A2:D6. Your 3 now points at the new, empty column, so the formula returns 0 and nothing flags it.

XLOOKUP points at the price column itself (C2:C6). When you insert a column, Excel shifts the reference to D2:D6 and the formula keeps working.

Looking left

VLOOKUP can only return columns to the right of the lookup column. XLOOKUP doesn't care about order. To find the ID for a product name in this table:

=XLOOKUP("Stapler",B2:B6,A2:A6)

That returns 1002. With VLOOKUP you'd have to rearrange the columns first, or switch to INDEX/MATCH (covered below).

Handling not-found cleanly

XLOOKUP's fourth argument replaces the usual IFERROR(VLOOKUP(...),"Not found") wrapper. That matters because IFERROR hides every error, including real problems like a mistyped range or a broken reference. if_not_found only handles the "no match" case.

Getting the most recent match

If an ID appears more than once, such as a log of price changes with the newest at the bottom, set the search direction to last-to-first:

=XLOOKUP(F2,A2:A100,C2:C100,"Not found",0,-1)

The 0 keeps exact matching, and -1 starts from the bottom. It returns the last matching row.

Returning several columns at once

Give XLOOKUP a return array with more than one column:

=XLOOKUP(F2,A2:A6,B2:C6)

For 1003, this fills two cells: Desk Lamp and 24.00. That "spilling" behavior needs Microsoft 365 or Excel 2021 or later, and the cells to the right must be empty. If they aren't, Excel shows a #SPILL! error. Clear those cells and the result fills in.

When VLOOKUP is still the right choice

VLOOKUP isn't broken, and nothing in Microsoft's documentation suggests it's going away. Keep it in these cases:

• The file goes to people on Excel 2016 or 2019. XLOOKUP will show #NAME? for them.
• You inherit an older workbook. If it works, leave it alone. Rewriting every old formula just to modernize it adds risk and gains little.
• The job is simple. The key is in the leftmost column, the layout won't change, and you remember to type FALSE.

To read an inherited VLOOKUP, check three things: what's in the first column of the table, what the column number counts to, and whether the last argument is FALSE or 0. If the last argument is missing or TRUE, check that the lookup column is sorted, because the formula may be approximating.

Fallback for older Excel: INDEX and MATCH

This pair works in every Excel version, looks left or right, and doesn't use a column number:

=INDEX(C2:C6,MATCH(F2,A2:A6,0))

Read it from the inside out. MATCH(F2,A2:A6,0) finds the position of the ID in the list (1003 is position 3). The 0 means exact match. INDEX(C2:C6,3) then returns the third item in the price column, 24.00.

To look left, swap the ranges: =INDEX(A2:A6,MATCH("Stapler",B2:B6,0)) returns 1002. It's a bit more to type than XLOOKUP, but it's the safest choice when the file has to open anywhere.

Common errors and fixes

• #N/A when the value looks present. Check for extra spaces and for numbers stored as text. Use TRIM() on text keys. If one list has numbers and the other has text, convert with VALUE() or fix the source column.
• #REF! in VLOOKUP. Your column number is larger than the number of columns in the table. A table of A2:C6 has only 3.
• Wrong values in VLOOKUP. The fourth argument is missing, so Excel did an approximate match. Add FALSE.
• #NAME? in XLOOKUP. The person's Excel version doesn't support it. Send them a VLOOKUP or INDEX/MATCH version.
• Results shift when you copy the formula down. Lock the ranges with dollar signs, for example =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6,"Not found").
• Formula won't accept commas. Some regional settings use semicolons between arguments, such as =VLOOKUP(F2;A2:C6;3;FALSE). Examples here use commas, the US convention.

Excel vs Google Sheets

Google Sheets has both functions. Its VLOOKUP uses an is_sorted argument instead of range_lookup, and you set it to FALSE for an exact match. Google explains this on its VLOOKUP help page.

Sheets also supports XLOOKUP, but check Google's own documentation for details on the match and search modes before relying on them, since behavior can differ from Excel. The same goes for what happens when you insert columns: test it in your own sheet rather than assuming it matches Excel.

Quick decision checklist and when to use something else

Run through these four questions:

1. Who opens the file? If anyone uses Excel 2016 or 2019, use VLOOKUP with FALSE or INDEX/MATCH. Otherwise XLOOKUP is fine.
2. Is the lookup column leftmost? If not, use XLOOKUP or INDEX/MATCH.
3. Will columns be added or moved? If yes, avoid a typed column number.
4. Do you need a "not found" message? XLOOKUP has one built in.

For joining large tables or repeating the same cleanup every week, Power Query is built for merging and refreshing data, and a pivot table is better for summarizing it. If many people need to update linked tables at once, a database is the better tool.

Quick answers

• Is XLOOKUP better? For most new work, yes, provided everyone's Excel supports it.
• Does Excel 2019 have XLOOKUP? No, according to Microsoft.
• Will VLOOKUP stop working? Nothing in Microsoft's documentation suggests it will.
• Is one faster? Speed rarely decides this for everyday sheets. Choose on version support and how fragile the layout is. If you have very large data, test both on your own file.