XLOOKUP vs VLOOKUP: which to use in Excel and Sheets

Use XLOOKUP for any new lookup in Excel for Microsoft 365, Excel 2021, Excel 2024 or Google Sheets. It finds an exact match unless you ask for something else, it can return a column on either side of the one it searches, and you point it at the column you want instead of counting across to it. Keep VLOOKUP for one reason: a file that will be opened in Excel 2019, Excel 2016 or anything older, because those versions don’t have XLOOKUP. Everything below was checked in October 2026 against Microsoft’s help pages for Excel for Microsoft 365 on Mac and Windows, and Google’s for Google Sheets on the web.

Which one to use

• A new formula, in a current version: XLOOKUP.
• A file that goes to someone on Excel 2019 or older: VLOOKUP with FALSE at the end, or INDEX and MATCH, which every version has.
• An old VLOOKUP that works: leave it until you next edit that sheet. With FALSE at the end and a table that won’t gain columns, it gives the same answer XLOOKUP would.

How each one reads

Both answer the same question: find this value in a list and give me something from the same row. Take a price list with headers in row 1 (ID, Name, Category, Price), product IDs in column A, names in B, categories in C and prices in D, on rows 2 to 200, and an order’s product ID typed in F2.

VLOOKUP takes the whole table and a column number:

=VLOOKUP(F2, $A$2:$D$200, 4, FALSE)

Read it as: look for F2 in the first column of A2:D200, return the fourth column, and match exactly (that’s the FALSE). The value has to sit in the table’s first column; Microsoft’s VLOOKUP page says so in as many words.

XLOOKUP takes the column to search and the column to return:

=XLOOKUP(F2, $A$2:$A$200, $D$2:$D$200)

Read it as: look for F2 in A2:A200 and return what’s on the same row of D2:D200. Exact match is the default, so there’s nothing to add for it. Three optional arguments can follow, as Microsoft’s XLOOKUP page sets them out: if_not_found (what to show when nothing matches), match_mode and search_mode.

What XLOOKUP does that VLOOKUP can’t

	VLOOKUP	XLOOKUP
Match when you leave it unset	Approximate, and assumes a sorted first column	Exact
Where the answer can be	To the right of the column searched	Either side
How you pick the answer	A column number you count	The column itself
When nothing matches	#N/A, unless wrapped in IFERROR	#N/A, or your own text in if_not_found
Columns returned by one formula	One	As many side-by-side columns as you give it
Search from the bottom up	No	Yes, with search_mode -1
Excel versions	All	Microsoft 365, 2021 and 2024; not 2019 or 2016
Google Sheets	Yes	Yes, since 2022

Three of those rows matter most from day to day.

It matches exactly by default

Leave out VLOOKUP’s last argument and it does an approximate match, which Microsoft says assumes the first column is sorted. On an unsorted list that can return a plausible wrong price rather than an error. XLOOKUP left alone matches exactly, and shows #N/A when there’s nothing to find.

It looks either way

VLOOKUP only looks from left to right. To find a product’s ID from its name, you need the ID column, which sits to the left of the names. XLOOKUP doesn’t mind: =XLOOKUP("Desk mat", $B$2:$B$200, $A$2:$A$200) searches the names and returns the ID.

It points at the column instead of counting

VLOOKUP’s 4 is a number you typed. Insert a column inside A:D and the prices move to column E, but the formula still returns the fourth column, which now holds something else. XLOOKUP points at D2:D200 itself, and Excel moves that reference with the column.

When VLOOKUP is still the one to use

Microsoft’s XLOOKUP page says plainly that XLOOKUP is not available in Excel 2016 and Excel 2019, and that you may still meet a workbook that uses it, made by someone on a newer version. Opened in one of those versions, an XLOOKUP shows the #NAME? error once Excel calculates it (the questions at the end have the detail). So when a workbook goes to a client, a supplier or a team still on Excel 2019 or older, or you don’t know what they run:

• write the lookup as VLOOKUP, with FALSE as the last argument every time;
• when the answer sits to the left, use INDEX and MATCH: =INDEX($A$2:$A$200, MATCH("Desk mat", $B$2:$B$200, 0));
• or keep your XLOOKUPs and send a copy with the results pasted as values: copy the column, open Paste Special with Ctrl+Alt+V on Windows or Ctrl+Cmd+V on a Mac, and choose Values.

XLOOKUP in Google Sheets

Google announced XLOOKUP for Sheets in August 2022. It takes the same arguments in the same order as Excel, under different names, with the same codes for the two modes:

Excel	Google Sheets	What it is
lookup_value	search_key	The value to find
lookup_array	lookup_range	The single column or row to search
return_array	result_range	The column, or columns, to return from
if_not_found	missing_value	What to show when nothing matches; #N/A if left out
match_mode	match_mode	0 exact (the default), -1 or 1 for the next smaller or larger, 2 wildcards
search_mode	search_mode	1 first to last (the default), -1 last to first, 2 and -2 for sorted data

So =XLOOKUP(F2, $A$2:$A$200, $D$2:$D$200, "Not found") works as it stands in both apps. Google’s XLOOKUP page adds two details: the lookup range must be a single row or column, and when the result range is wider than one column, Sheets returns its whole row at the match. Sheets’ VLOOKUP has the same trap as Excel’s: leave out its last argument, is_sorted, and it assumes a sorted first column, so end it with FALSE there too.

Rewrite a VLOOKUP as an XLOOKUP

Take =VLOOKUP(F2, $A$2:$D$200, 4, FALSE) and rebuild it one argument at a time:

1. Keep the lookup value. F2 stays first: =XLOOKUP(F2,
2. Take the table’s first column as the lookup array. VLOOKUP always searches the first column of its table, here A2:A200, so that becomes the second argument: $A$2:$A$200.
3. Count across to find the return array. Column 4 of A:D is D, so the third argument is $D$2:$D$200. Give it the same rows as the lookup array.
4. Drop the FALSE. XLOOKUP matches exactly by default. If the old formula ended in TRUE, or had no fourth argument, it was doing an approximate match. Check whether that was meant before you copy it; if it was, add -1 as the match_mode, which finds the exact value or the next smaller one: =XLOOKUP(F2, $A$2:$A$200, $D$2:$D$200, "Not found", -1).
5. Move any IFERROR text into if_not_found. =IFERROR(VLOOKUP(F2, $A$2:$D$200, 4, FALSE), "Not found") becomes =XLOOKUP(F2, $A$2:$A$200, $D$2:$D$200, "Not found"). One difference: IFERROR hides every error, while if_not_found covers only a missing match, so a broken reference still shows.
6. Check it against the old one. Put the XLOOKUP in an empty column beside the VLOOKUP, fill it down, and delete the VLOOKUP once the two columns agree row for row.

Build it without the mouse

The mouse way is to type the function, drag down each column, then click back into the formula to add the $ signs. From the keyboard it’s the same formula with your hands kept where they are. With the price list in A:D and the order IDs running down column F, start in G2:

1. Type =XLO and press Tab to take XLOOKUP from the list; Excel adds the opening parenthesis.
2. Press Left once to point at F2, then type a comma.
3. Press Left until the moving border sits on A2, then Ctrl+Shift+↓Down on Windows or Cmd+Shift+↓Down on a Mac to take the column to its last row. Press F4 on Windows or Cmd+T on a Mac to lock it, so A2:A200 becomes $A$2:$A$200, and type a comma.
4. Do the same for D2: Left until the border sits on D2, Ctrl+Shift+↓Down or Cmd+Shift+↓Down, F4 or Cmd+T, and a comma.
5. Type "Not found" and the closing parenthesis, and press Enter on Windows or Return on a Mac.
6. Fill it down. Excel has moved to G3, so press Left, then Ctrl+↓Down or Cmd+↓Down to jump to the last order ID, Right to step into column G, and Ctrl+Shift+↑Up or Cmd+Shift+↑Up to select back up to the formula. Press Ctrl+D or Cmd+D.
7. Press Ctrl+` (on both systems) to show the formulas, and check that F2 became F3, F4 and so on while the $ references stayed put. Press it again to go back to the results.

Google Sheets takes the same route: the arrow keys point at cells while you type a formula, and F4 locks a reference (Fn+F4 on a Mac). If you know the addresses by heart, typing $A$2:$A$200 is just as good as pointing.

Tip: turn the price list into a table first, with Ctrl+T on either system. The formula then reads =XLOOKUP(F2, Table1[ID], Table1[Price]), with your own headers in the brackets; the references grow as rows are added, and there’s nothing to lock.

The keys in these steps are on the Excel Shortcut Sheet and the Google Sheets Shortcut Sheet, Mac and Windows side by side. For the rest of Excel’s keyboard, from filling and pasting values to inserting rows, see Excel keyboard shortcuts for Mac and Windows, by task. More on formulas, lookups and pivot tables is in Spreadsheets.