XLOOKUP finds a value in one column and brings back the matching value from another. Paste your tables below to get the exact formula for your data.
100% in your browser: your data never leaves your device
| A | B | C | E | F | ||
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Find | Price | |
| 2 | P-101 | Pens | 1.20 | P-104 | 2.25 | |
| 3 | P-102 | Paper | 3.99 | |||
| 4 | P-103 | Stapler | 6.50 | |||
| 5 | P-104 | Tape | 2.25 | |||
| 6 | P-105 | Folders | 0.80 |
=XLOOKUP(E2, A2:A6, C2:C6) and shows 2.25.Paste or upload your two tables, or try a sample. The formula appears under the result, written for your columns and ready to copy into Excel.
The first four use the example sheet at the top of the page: codes in column A, products in B, prices in C.
Add the text you want as a fourth part. It shows when the value isn't found:
=XLOOKUP(E2,A2:A6,C2:C6,"Not found") Add $ signs so the ranges stay put when you fill the formula down. Without them, each row's ranges slide down one row and miss the top
of the table.
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6) The column you bring back can be on either side. Find the code for a product name:
=XLOOKUP("Tape",B2:B6,A2:A6) Give a range that is more than one column wide and the answers spill across:
=XLOOKUP(E2,A2:A6,B2:C6) To match on two columns, say first names in column A and last names in B, join the two names you're looking for with & and join the columns the same way:
=XLOOKUP(E2&F2,A2:A100&B2:B100,C2:C100) For a log where the same item appears many times, such as price changes, search from the bottom:
=XLOOKUP(E2,A2:A100,C2:C100,"Not found",0,-1) You only need the first three. The other three are there when you need them:
| Part | What it does | Values |
|---|---|---|
if_not_found | What to show when there's no match | Any text or value, e.g. "Not found" |
match_mode | How exact the match must be | 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards |
search_mode | Which way to search | 1 top to bottom (the default), -1 bottom to top, 2 and -2 fast search on sorted data |
#N/A: the value isn't there exactly. The usual cause is a hidden space or a number stored as text. The tool above names the reason
for each value it can't find, and VLOOKUP not working? walks through the same causes.#NAME?: your Excel doesn't have XLOOKUP (Excel 2019 and older). Use INDEX and MATCH instead.#VALUE!: the two ranges are different sizes, such as A2:A100 and C2:C90. Make them the same.#SPILL!: you're bringing back several columns but the cells to the right aren't empty. Clear them.$ (try the lesson).Each lesson takes a few minutes in a spreadsheet in your browser, and checks your formula as you go:
&.Still deciding between the two? See XLOOKUP vs VLOOKUP.
three arguments, any direction
=XLOOKUP(lookup_value, lookup_array, return_array)
lookup_value
The value you are looking for.
✎ Trim spaces first!
lookup_array
The column (or row) searched.
✎ Lock it with $ ($A$2:$A$500)
return_array
Where the answer comes from: left, right, above or below.
✎ Several columns spill across!
XLOOKUP vs VLOOKUP side by side: exact match by default, looking left, no column numbers, built-in not-found text. With formulas, and when VLOOKUP still fits.
XLOOKUP finds a value in one column (or row) and brings back the value in the same position from another. The basic form is =XLOOKUP(what to find, where to look, what to bring back), for example =XLOOKUP(E2,A2:A6,C2:C6).
Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Excel 2019 and older do not have it and show #NAME? instead. In those versions use INDEX and MATCH.
Yes. Google Sheets has XLOOKUP with the same arguments, so the formulas on this page work there too.
Join the two values you are looking for with &, and join the two columns the same way: =XLOOKUP(E2&F2,A2:A100&B2:B100,C2:C100). It finds the row where both columns match.
Add a fourth argument with the text to show instead: =XLOOKUP(E2,A2:A100,C2:C100,"Not found"). If you still see #N/A for values that look the same, check for extra spaces or numbers stored as text.
For new work, yes. XLOOKUP matches exactly by default, can bring back a column to the left, does not break when you insert a column, and has a built-in not-found message. VLOOKUP is still useful when a file must work in older Excel.
No. "tape" and "TAPE" count as the same value.
No. The tool runs in your browser and your data never leaves your device.
Know someone fighting with a spreadsheet? Send them this page.