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. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “use xlookup function in excel”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the outcome you expected before you touch the real workbook. 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. The point is a data step that keeps your analysis trustworthy, but the practical win is that someone else can open the file and understand what happened without asking you.
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. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “use xlookup function in excel”, 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.
Other ways people ask this
This is also commonly searched as “xlookup function in excel” and “how to use the xlookup function in excel”. They describe the identical operation, so you are in the right place no matter how you phrased it.
Why do people search for this in so many different ways?
Because the same task has many names. “xlookup function in excel”, “how to use the xlookup function in excel” all point at the one operation explained on this page, which is why they all lead here.