XLOOKUP vs VLOOKUP: the differences, with examples

The two syntaxes

• =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The key difference is how you tell Excel what to return. VLOOKUP takes a whole table and a column number: "return the 3rd column". XLOOKUP takes two separate ranges: "look here, return from there". Everything else follows from that. In the example below, both formulas look up the same code in the same five-row price list, so you can compare them line by line.

Worked example: the same lookups, both ways

Type a different code into D8 and every lookup below it updates.

Worked example: the same lookups, both ways

	A	B	C	D
1	Code	Product	Price	In stock
2	P-101	Notebook A5	4.50	320
3	P-102	Gel pens (10)	6.90	145
4	P-103	Desk lamp	38.00	24
5	P-104	Stapler	12.75	60
6	P-105	Copy paper (500)	5.20	410
7
8	Code to look up			P-103
9	Price with VLOOKUP			38.00
10	Price with XLOOKUP			38.00
11	Stock with XLOOKUP			24
12	Code of "Stapler" (lookup to the left)			P-104
13	Unknown code, XLOOKUP fallback			Not found
14	Unknown code, VLOOKUP + IFERROR			Not found

Difference 1: exact match by default

VLOOKUP's fourth argument is optional, and if you leave it out, VLOOKUP does an approximate match. On unsorted data that returns a wrong value without any error, the most dangerous kind of mistake. You must write FALSE (or 0) every time: =VLOOKUP(D8,A2:D6,3,FALSE). XLOOKUP does an exact match unless you ask for something else, so =XLOOKUP(D8,A2:A6,C2:C6) is already safe.

Difference 2: columns can be inserted

VLOOKUP's column number is a typed constant. Insert a column inside the table, say a "Supplier" column between Product and Price, and 3 now points to the wrong column. XLOOKUP refers to the return column directly, so it moves with the data and keeps working.

Difference 3: looking to the left

VLOOKUP can only search the first column of the table and return something to its right. To find the code of the product "Stapler", the code sits to the left of the product name, so VLOOKUP cannot do it without rearranging the data or switching to INDEX/MATCH. XLOOKUP does not care about direction: =XLOOKUP("Stapler",B2:B6,A2:A6) returns P-104.

Difference 4: a built-in "not found" value

When the value does not exist, both functions return #N/A. With VLOOKUP you wrap it: =IFERROR(VLOOKUP(…),"Not found"). But IFERROR also hides other errors, such as a typo in a range. XLOOKUP has its own fourth argument for this: =XLOOKUP("P-999",A2:A6,C2:C6,"Not found"). Use IFNA rather than IFERROR around VLOOKUP if you want to catch only the missing-value case.

Approximate matches: tax brackets and discount tiers

Both functions can find the right band in a table of thresholds, such as this discount table:

Order from ($)	Discount
0	0%
500	2%
2,000	5%
5,000	8%

For an order of $2,750, =VLOOKUP(2750,G2:H5,2,TRUE) returns 5%. It needs the thresholds sorted in ascending order. The XLOOKUP version is =XLOOKUP(2750,G2:G5,H2:H5,,-1), where match mode -1 means "exact match, or the next smaller value".

Extra XLOOKUP features VLOOKUP does not have

• Search from the bottom. Search mode -1 finds the last match, for example the most recent price for a product in a list sorted by date: =XLOOKUP("P-103",A2:A50,C2:C50,,0,-1).
• Several columns at once. If the return array is several columns wide, such as B2:D6, XLOOKUP spills product, price and stock into neighbouring cells with one formula.
• Horizontal tables. XLOOKUP works on rows as well as columns, so it also replaces HLOOKUP.

Converting a VLOOKUP into an XLOOKUP

Keep the lookup value. Replace the table with its first column as the lookup array. Replace the column number with the column it pointed to as the return array. Drop the FALSE. So =VLOOKUP(D8,A2:D6,3,FALSE) becomes =XLOOKUP(D8,A2:A6,C2:C6). If the old formula was wrapped in IFERROR, move the fallback text into the fourth argument. Test both versions side by side on a few rows before you replace a whole column.

When VLOOKUP is still the better choice

XLOOKUP does not exist in Excel 2019 and earlier. In those versions a file with XLOOKUP shows #NAME?. If you share workbooks with clients, auditors or colleagues on older versions, VLOOKUP (or INDEX/MATCH, which works everywhere) is the safe option. Existing models full of correct VLOOKUPs also do not need rewriting. For new work in Microsoft 365, XLOOKUP is clearer and harder to break.

Common mistakes

• Leaving out FALSE in VLOOKUP. The default approximate match returns plausible but wrong values on unsorted data.
• Ranges of different lengths in XLOOKUP. lookup_array and return_array must have the same number of rows.
• Forgetting $ when filling down. A2:A6 becomes A3:A7 in the next row. Lock it as $A$2:$A$6.
• Codes that look the same but are not. A code stored as the number 1001 does not match the text "1001", and trailing spaces break matches too.
• Expecting all matches. Both functions return only the first match. Use FILTER or SUMIFS when there can be several rows.