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. 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 tool above 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
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
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. 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. Here is the takeaway for “use xlookup function in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.
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
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.