How to Use XLOOKUP in Excel: Formula, Examples and Fixes

If you've ever had two lists and needed to pull a value from one into the other, such as a description for a product code or a contact name for an order number, XLOOKUP is the tool built for the job. I think of it as asking Excel a question: here's what I know, now go and find me the matching detail.

This guide sticks to what Microsoft's help page for XLOOKUP says, with cell references kept simple so you can swap in your own.

What XLOOKUP does in Excel

Microsoft describes XLOOKUP as a function that searches a range or array for a match and returns the item from a second range in the same position. In plain English, you give it a value to look for, the column to look in, and the column to bring the answer back from.

One practical point first. Microsoft's page notes that XLOOKUP is not available in Excel 2016 and Excel 2019, even though other parts of the same page list those versions. If the function isn't recognised on your machine, that's the first thing to check. A workbook made in a newer version can still be opened in the older ones.

The XLOOKUP formula pattern, argument by argument

Here's the pattern exactly as Microsoft writes it:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: the thing you're searching for, such as a cell holding an order number.
• lookup_array: the column or row Excel should search.
• return_array: the column or row the answer comes from.
• if_not_found: optional text or value to show when nothing matches. Leave it out and Excel shows #N/A.
• match_mode: optional. It decides how strict the match is.
• search_mode: optional. It decides which direction Excel searches in.

The first three are all you need most days. The last three are extras you add when a simple match isn't enough.

A simple XLOOKUP example you can copy

Imagine a small sheet where column A holds order numbers, column B holds customer names, and you've typed an order number into cell E2. To bring back the customer name, you'd write something like this in the cell beside it:

=XLOOKUP(E2,A2:A9,B2:B9)

Excel looks for the value in E2 inside A2 to A9 and hands back the matching name from B2 to B9. Microsoft's own first example works the same way, finding a country in one column and returning its telephone country code from another. Because exact match is the default, you don't need to add anything else.

A neat bonus is that the lookup column can sit to the right of the return column. Microsoft points this out in its approximate match example, and it's one of the things people used to find awkward with older lookup formulas.

Showing a friendly message when XLOOKUP finds nothing

By default a failed search gives #N/A, which looks alarming in a client sheet. Add the fourth argument and you can show your own wording instead:

=XLOOKUP(E2,A2:A9,B2:B9,"Not found")

Put the message in quotation marks, as Microsoft notes for text. It's a small change that makes a shared sheet kinder to whoever opens it next.

Choosing the XLOOKUP match_mode and search_mode

These two options are where XLOOKUP gets more flexible. Microsoft lists the values like this:

Setting	Value	What it does
match_mode	0	Exact match. This is the default.
match_mode	-1	Exact match, or the next smaller item if there isn't one.
match_mode	1	Exact match, or the next larger item if there isn't one.
match_mode	2	Wildcard match, where *, ? and ~ have special meaning.
search_mode	1	Search from the first item to the last. This is the default.
search_mode	-1	Reverse search, starting at the last item.
search_mode	2	Binary search, with the search column sorted in ascending order.
search_mode	-2	Binary search, with the search column sorted in descending order.

The reverse search is useful when a value appears more than once and you want the latest entry. A word of caution on the binary options: Microsoft says that if the data isn't sorted the way the setting expects, the results will be invalid. Stick to the default unless you know your list is sorted.

Returning more than one column with XLOOKUP

It can bring back several columns in one go if your return range is wider. Microsoft's second example returns both an employee name and a department from a single formula, by making the return range two columns wide. If you only want one detail, keep the range to one column.

This is a lovely time-saver for contact sheets, where you might want name, phone and email from a single lookup. If you're ordering that sheet first, my guide to sorting in Google Sheets covers the same idea for teams that work there.

Fixing an XLOOKUP that returns #N/A or the wrong answer

Work through these in order:

1. Check the spelling and spaces. A trailing space in either list stops an exact match.
2. Check both lists hold the same kind of data. A number in one and text in the other won't match.
3. Make sure the lookup range and return range are the same size.
4. Look at the match_mode. Wildcards only work when it's set to 2.
5. If you used a binary search, check the list is sorted the way Microsoft says it must be.
6. Add the if_not_found argument so you can tell a missing match from a broken formula.

If Excel says it doesn't recognise the function at all, go back to the version point above. Menus and function lists vary by version, so check which release of Excel you have.

Using XLOOKUP in a small business spreadsheet

• Pull a client's contact details into an order tracker from a master client list.
• Match product codes on an order sheet to descriptions in a stock list.
• Find which campaign a lead came from by looking up its source code.
• Bring a staff member's name and department together from an ID.

If you'd like an assistant working inside the workbook while you build formulas, I've covered how to install Claude for Excel and prompts to try. Whichever route you take, keep one master list and let every other sheet look things up from it. When you change a detail in one place, it's right everywhere.

Frequently asked questions

How do I use XLOOKUP in Excel?

Type =XLOOKUP( then give it the value to find, the range to search and the range to return, for example =XLOOKUP(E2,A2:A9,B2:B9). Press Enter and Excel returns the matching item.

What is the XLOOKUP formula?

Microsoft gives it as =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Only the first three parts are required.

Why does XLOOKUP show #N/A?

It means no valid match was found and you haven't added an if_not_found message. Check for extra spaces, mismatched data types and spelling, or add your own message as the fourth argument.

Is XLOOKUP available in every version of Excel?

Microsoft's page says XLOOKUP is not available in Excel 2016 and Excel 2019, although other parts of the page list them. Check your version if the function isn't recognised.

Can XLOOKUP look to the left?

Yes. Microsoft notes the lookup column can sit to the right of the return column, which is one reason people prefer it to older lookup formulas.