XLOOKUP

How XLOOKUP works

XLOOKUP is the modern replacement for VLOOKUP. You give it three things: the value you want to find, the column or row to search in, and the column or row to return a result from. Done. No col_index_num, no first-column constraint, no range_lookup gotcha.

Because lookup_array and return_array are separate arguments, XLOOKUP can search a column on the right and return a value from a column on the left, something VLOOKUP physically cannot do. And because if_not_found is built in, you don't need to wrap it in IFERROR to avoid #N/A errors.

If you're on Excel 2021, Microsoft 365, or Excel for the web, XLOOKUP should be your default lookup function. It's cleaner, safer, and more flexible.

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

Arguments

XLOOKUP basic: find product price by code

You work in sales at TechDirect. A customer wants the price of product P-1042. Your catalog has 500 rows. One formula finds it instantly, and you don't need to count columns.

=XLOOKUP(G2,$A$2:$A$9,$D$2:$D$9)

• •G2 → the product code you're looking up
• •$A$2:$A$9 → the column to search in (the Code column)
• •$D$2:$D$9 → the column to return from (the Price column)
• •No col_index_num → you point at the return column directly. No counting.

XLOOKUP with if_not_found: built-in error handling

With VLOOKUP, missing matches return #N/A and you have to wrap the whole formula in IFERROR. XLOOKUP has a fourth argument called if_not_found. Pass a fallback value and you're done. No nesting, no IFERROR, no extra parentheses to balance.

=XLOOKUP(E2,$A$2:$A$8,$B$2:$B$8,"Not found")

• •E2 → the order's product code (lookup_value)
• •$A$2:$A$8 → the Code column to search in (lookup_array)
• •$B$2:$B$8 → the Price column to return from (return_array)
• •"Not found" → shown instead of #N/A when the code doesn't exist

XLOOKUP across sheets: pulling data from another tab

Your employee list is on Sheet1. Salaries live on Sheet2 to keep them restricted. XLOOKUP works across sheets exactly like a same-sheet lookup. Just prefix the lookup_array and return_array with the sheet name.

=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)

• •A2 → the employee ID on Sheet1
• •Sheet2!A:A → the Employee ID column on Sheet2 (where to look)
• •Sheet2!B:B → the Salary column on Sheet2 (what to return)
• •No match_mode needed → exact match is the default for XLOOKUP

Sheet1 G2 looks up E-1001 on Sheet2 and pulls back $72,000. The Sheet2! prefix tells XLOOKUP exactly which tab to search. The rest of the formula works identically to a same-sheet lookup.

XLOOKUP with match_mode -1: commission tiers

Commission tiers. Tax brackets. Grade scales. When you need to find which RANGE a value falls into rather than an exact match, use XLOOKUP's fifth argument: match_mode.

Pass -1 and XLOOKUP returns the next-smaller value if no exact match is found, perfect for tiered lookups. And unlike VLOOKUP's TRUE, XLOOKUP doesn't require the tier table to be sorted ascending, though sorting still keeps it readable.

=XLOOKUP(B4,$E$2:$E$5,$F$2:$F$5,,-1)

• •B4 → the rep's monthly sales (lookup_value)
• •$E$2:$E$5 → the tier thresholds (lookup_array)
• •$F$2:$F$5 → the tier rates (return_array)
• •[if_not_found] → skipped: leave blank with the empty 4th argument
• •-1 → match_mode: exact match, or next smaller if not found

XLOOKUP looks left: pull any column from any direction

The single biggest reason to switch from VLOOKUP to XLOOKUP. VLOOKUP can only return values from columns to the right of the lookup column. The lookup must always be in column 1 of your range. XLOOKUP doesn't care; lookup_array and return_array are independent.

Say you have a product NAME and you need its CODE. The Code column sits to the LEFT of Name in your catalog. VLOOKUP physically can't do this. XLOOKUP does it cleanly.

=XLOOKUP(F2,$B$2:$B$9,$A$2:$A$9)

• •F2 → the product name you're searching for
• •$B$2:$B$9 → the Name column (lookup_array)
• •$A$2:$A$9 → the Code column to RETURN: sits LEFT of the Name column
• •No match_mode → exact match by default: leave it off

Where people go wrong

1. 01
   Mismatched lookup_array and return_array sizes

   You set lookup_array to A2:A100 but return_array to B2:B50. XLOOKUP returns #VALUE! because the two arrays must have the same number of rows (or columns, for horizontal lookups).

   Fix: Always make lookup_array and return_array the same length. The cleanest pattern: use whole columns ($A:$A, $B:$B) or matching row ranges ($A$2:$A$100, $B$2:$B$100).
2. 02
   Skipping if_not_found and getting #N/A

   The whole point of switching to XLOOKUP was to avoid IFERROR. But if you forget to pass the if_not_found argument, missing matches still return #N/A and your report still looks broken.

   Fix: Pass if_not_found on every XLOOKUP. Even if the value should always exist, set it to "Not found" or 0 so a stray missing match never crashes the worksheet.
3. 03
   Forgetting $ signs when copying down

   XLOOKUP doesn't suffer from VLOOKUP's column-shift problem, but you still need $ signs to lock the lookup_array and return_array. Without them, the ranges shift one row down with each drag and the lookup eventually misses entries.

   Fix: Lock both arrays with $ signs: =XLOOKUP(F2,$B$2:$B$9,$A$2:$A$9). Press F4 with the cursor inside a reference to add the $ signs automatically.
4. 04
   Using XLOOKUP in a workbook shared with older Excel users

   XLOOKUP is only available in Excel 2021, Microsoft 365, and Excel for the web. If a colleague opens your file in Excel 2019 or older, every XLOOKUP becomes #NAME?. Even if you saved the file with values intact, refreshing breaks them.

   Fix: Check who else needs to edit the workbook. If anyone is on Excel 2019 or earlier, fall back to VLOOKUP or INDEX MATCH for compatibility.

Notes

• XLOOKUP is case-insensitive. "apple" and "APPLE" return the same result
• Pass match_mode 2 to enable wildcard matching (use ? for any single character, * for any sequence)
• XLOOKUP can return an entire ROW or COLUMN by passing a range as return_array. Useful with dynamic arrays.
• For horizontal lookups, just pass row ranges as lookup_array and return_array. XLOOKUP works in any direction.
• Use search_mode -1 to search from the bottom up. Handy for finding the most recent matching entry.
• Available in Excel 2021, Microsoft 365, and Excel for the web. Older versions return #NAME?. Fall back to VLOOKUP or INDEX MATCH for those workbooks.
• Approximate match modes (1 and -1) work without requiring the lookup table to be sorted, unlike VLOOKUP's TRUE

What to learn next

Now prove it