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.
Syntax
Arguments
| Argument | 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 match is found (avoids #N/A). |
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
In the result cell type =XLOOKUP( and click the cell holding the value to find.
Select the column that contains that value as lookup_array.
Select the column to pull the answer from as return_array.
Optionally add a fourth argument in quotes to replace #N/A with your own text.
Press Enter; copy the formula down the column.
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. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “use xlookup excel”, 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
Prices sit in B2:B100 keyed by SKU in A2:A100. To get the price for the SKU in cell E2: =XLOOKUP(E2, A2:A100, B2:B100). If the SKU is missing it returns #N/A; add a fourth argument to show text instead: =XLOOKUP(E2, A2:A100, B2:B100, "not found"). Reach for XLOOKUP whenever you would have written a VLOOKUP or an INDEX/MATCH: it is shorter, survives inserted columns, and fails gracefully with if_not_found. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. If you take one thing from this page on “use xlookup excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
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. - Hard-coding the lookup value instead of pointing at a cell, so the formula cannot be filled down.
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?
No exact match was found. Either the value really is absent, the cells differ by stray spaces (wrap lookup_value in TRIM), or numbers are stored as text. Add a fourth argument to return friendly text instead of #N/A.
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.
Other ways people ask this
On the way here you may have searched this as “xlookup formula in excel with example”, “xlookup formula excel”, “use xlookup in excel” and “using xlookup in excel” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “xlookup formula in excel with example”, “xlookup formula excel”, “use xlookup in excel” all point at the one operation explained on this page, which is why they all lead here.