Excel Help
XLOOKUP: Find Anything in an Excel Table in Seconds
Stop scrolling through long lists. XLOOKUP finds the value you need and brings it back to you, even if you have never used a formula before.
Have You Ever Scrolled for a Price?
Imagine you have a long list of products with their prices. A customer asks, "How much is the desk lamp?" So you scroll, squint and hope you land on the right row.
Now imagine typing Desk lamp into a cell and seeing the price appear next to it, instantly. That is what XLOOKUP does.
You do not need to be an Excel expert. If you have ever used the Find box (Ctrl + F) to locate something, you already understand the idea. XLOOKUP simply does the finding and the fetching for you.
Is XLOOKUP Useful for Me?
Probably, if you ever do things like this:
- Look up a price, name or phone number from a list
- Match an employee ID to a name
- Find an invoice and see whether it is paid
- Combine information from two different lists
If you copy and paste the same data back and forth between lists, there is a good chance XLOOKUP can do that work for you.
See It in Action
Here is a small price list. Column A has the products and column B has the prices.
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Product | Price | Search for | Price | |
| 2 | Notebook | 4.50 | Desk lamp | 24.90 | |
| 3 | Pen set | 7.20 | |||
| 4 | Desk lamp | 24.90 | |||
| 5 | Stapler | 9.00 |
In cell E2 we type one formula. Excel looks for "Desk lamp" (the orange cell) in column A, finds it in row 4, and brings back the price from column B (the green cell).
What you search for Where it was found What comes back
The Formula, in Three Simple Pieces
Here is the formula that did it:
=XLOOKUP( D2, A2:A5, B2:B5 )
It looks scary at first, but it is just three answers to three questions:
What am I looking for?
D2 is the cell where you typed "Desk lamp".
Where should Excel look?
A2:A5 is the column with all the product names.
What should I bring back?
B2:B5 is the column with the prices.
That is all. Change the name in D2 to "Stapler" and the price changes to 9.00 on its own.
Comma or Semicolon? It Depends on Where You Live
You may have copied a formula from a website and got an error, even though it looked perfect. The cause is often tiny: the separator between the pieces.
Excel uses the regional settings of your computer to decide which character separates the pieces of a formula:
| Style | Separator | Typical regions | Example |
|---|---|---|---|
| American | Comma , | USA, UK, Canada, Australia | =XLOOKUP(D2, A2:A5, B2:B5) |
| European | Semicolon ; | Finland, Sweden, Germany, France, Spain | =XLOOKUP(D2; A2:A5; B2:B5) |
The formula does exactly the same thing in both styles. Only the character between the pieces is different.
What to do: If a formula from this page or from the internet gives an error, replace every comma with a semicolon, or the other way around. Most online examples use commas. If your Excel is set to a European region, type semicolons instead.
The same applies to the optional "Not found" message from
later in this article:
=XLOOKUP(D2; A2:A5; B2:B5; "Not found")
How to Use It Yourself
- Click the empty cell where you want the answer to appear.
- Type =XLOOKUP(
- Click the cell that has what you are searching for, then type a comma.
- Select the column to search in, then type a comma.
- Select the column with the answers, type a closing bracket ) and press Enter.
Why XLOOKUP Beats VLOOKUP
You may have heard of VLOOKUP. It has been Excel's lookup tool for decades, and XLOOKUP is its newer, friendlier replacement. Both can do the same job, but XLOOKUP removes the usual headaches.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Counting columns | You must count which column number to return | You simply click the column |
| Looking left | Only works if the answer is to the right | Works in any direction |
| Adding a column later | Can quietly return the wrong data | Keeps working correctly |
| Exact match | You must remember to ask for it (FALSE) | Exact match is the default |
| Nothing found | Shows an error like #N/A | Can show your own message instead |
The biggest win for beginners is the first row. With VLOOKUP you have to count columns and type a number. If you count wrong, or someone inserts a column later, you get wrong answers without any warning. With XLOOKUP there is nothing to count.
What If the Item Is Not in the List?
If you search for "Calculator" and it is not on the list, Excel shows an error that looks like #N/A. With XLOOKUP you can add a fourth piece to show a friendly message instead:
=XLOOKUP(D2, A2:A5, B2:B5, "Not found")
Now, instead of an error, you will see the words Not found. You can write any text you like between the quotation marks.
Common Problems and How to Fix Them
You see #N/A
Excel could not find what you searched for. Check the spelling, and look for extra spaces at the start or end of the text. "Desk lamp " (with a space) is not the same as "Desk lamp". Or add the "Not found" message shown above.
Excel says there is a problem with your formula
Check the separators. If your Excel uses European settings, use semicolons (;) instead of commas (,), as explained above.
You see #NAME?
Your version of Excel does not have XLOOKUP. It is available in Microsoft 365, Excel 2021 and newer, and Excel for the web. Older versions need the older VLOOKUP or INDEX and MATCH instead.
You see #VALUE!
The "look in" column and the "bring back" column are not the same size. For example, A2:A5 paired with B2:B9. Make sure both ranges have the same number of rows.
The numbers look right but nothing matches
Sometimes a number is stored as text, so "1001" and 1001 look the same but Excel treats them as different. If the cell has a small green triangle or the number is left-aligned, convert it to a real number.
The Short Version
- XLOOKUP finds something in one column and returns the matching value from another.
- The formula is =XLOOKUP(find this, in here, bring back this).
- It is easier and safer than VLOOKUP, because there is nothing to count.
- Add a fourth piece, such as "Not found", for a friendly message instead of an error.
- Use commas in American-style Excel and semicolons in European-style Excel.