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. Treat “xlookup two columns” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected columns shows up while it is still harmless. 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 turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.
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. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
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. Treat “xlookup two columns” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
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
Platform & version notes
- Several of those searches mention Google Sheets — and the good news is that "xlookup two columns" works there almost identically: the formula syntax carries over unchanged, only the surrounding menus sit across the top of the screen instead of in a ribbon.
Can I do this in Google Sheets too?
Yes. "xlookup two columns" carries over to Google Sheets with the same formula and almost the same steps — the menus are arranged differently, but the result is identical.