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. 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. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “xlookup multiple sheets”. Start on a copy or a tiny sample, keep the affected sheet 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
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. 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, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “xlookup multiple sheets”: 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.