In Excel: use =XLOOKUP(lookup_value, lookup_array, return_array) — it searches one column and returns the matching value from another, in any direction, with an exact match by default. Function availability: XLOOKUP: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
lookup_value | required | The value to search for. |
lookup_array | required | The range to search in. |
return_array | required | The range to return a result from. |
if_not_found | optional | Value to return when no valid match exists. It does not replace an empty matched cell or other formula errors. |
match_mode | optional | 0 exact (default), -1/1 next smaller/larger, 2 wildcard. |
search_mode | optional | 1 first-to-last (default), -1 last-to-first, 2/-2 binary search ascending/descending. |
Related functions
Function availability: XLOOKUP: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support.
Create the three-SKU example and place the lookup key in E2. Keep the result cell outside A2:B4.
Enter =XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"not found"). The dollar signs hold the source ranges fixed when you fill the formula down.
Check a present key, an absent key and a zero price separately: SKU-2 gives 19.5, SKU-9 gives not found and SKU-1 gives 0.
Test SKU-3 with an empty return cell. Use the IF return-array version only when an existing blank price should display an empty string.
For repeated keys, decide whether the first or last occurrence is wanted. =XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"not found",0,-1) searches from the last row; it does not sum all matching rows.
What this does
XLOOKUP replaces VLOOKUP and HLOOKUP with one function that looks left or right, top or bottom, and defaults to an exact match instead of an approximate one. You give it the value to find, the column to search, and the column to return — no column-index counting, no FALSE flag to remember. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “xlookup with if”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Put SKU-1, SKU-2 and SKU-3 in A2:A4. Put the numeric prices 0 and 19.5 in B2:B3 and leave B4 empty. With SKU-2 in E2, =XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"not found") returns 19.5; SKU-1 returns the legitimate price 0; SKU-9 returns not found. SKU-3 exists but its return cell is empty, so the basic formula displays 0 rather than the not-found text. To preserve that distinction, use =XLOOKUP(E2,$A$2:$A$4,IF($B$2:$B$4="","",$B$2:$B$4),"not found"). SKU-3 now produces an empty string while SKU-1 still returns numeric 0. The empty string is a formula result, not a truly empty output cell. Separate ranges remove the need to count a return-column index. Check the missing-key policy and the blank-return policy separately so a lookup can distinguish an absent product, a missing price and a genuine zero. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Function availability: XLOOKUP: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. For “xlookup with if”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Selecting ranges of different heights for lookup_array and return_array, which throws a
#VALUE!error. - Assuming approximate match like
VLOOKUP—XLOOKUPdefaults to exact, so set match_mode to -1 or 1 only when you need the nearest value. - Leaving the source ranges relative while copying down, which moves the lookup table and can lose matches.
- Replacing every zero with an empty string to hide blank prices; that also hides genuine numeric zeros. Test the return cells for blanks instead.
- Using binary search_mode 2 on an unsorted ascending range or -2 on an unsorted descending range; Microsoft warns that this can return invalid results.
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
For most lookups, yes: it can return columns to the left, defaults to exact match, and has a built-in if_not_found argument. VLOOKUP still works for older files that must open in Excel 2019 or earlier, where XLOOKUP is unavailable.
Why does XLOOKUP return #N/A?
With the default exact match, no matching key was found. Check that the key exists and compare its data type and spaces on both sides. TRIM on the lookup value alone cannot remove spaces from source keys. Keep identifiers with meaningful leading zeros as text. Use if_not_found for an expected missing key after diagnosing the mismatch; it does not fix a broken reference or incompatible ranges.
Why does XLOOKUP return 0 instead of blank?
A matched empty return cell is different from a missing lookup key. The fourth argument handles the missing key. For the sample prices, =XLOOKUP(E2,$A$2:$A$4,IF($B$2:$B$4="","",$B$2:$B$4),"not found") returns an empty string for an existing blank price and preserves a real numeric zero. Do not use a blanket result=0 test when zero is valid data.
How do I return the last match or use approximate match?
Set search_mode to -1 for the last occurrence, keeping match_mode 0 for an exact key. Set match_mode -1 or 1 only when the next smaller or larger item is intended. Binary search modes 2 and -2 have a separate sorting requirement: ascending for 2, descending for -2.
Can XLOOKUP return multiple columns?
Yes — select several columns as return_array and the result spills across that many cells, as long as the cells to the right are empty.
Which Excel release does this formula require?
Function availability: XLOOKUP: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support.