What it does
XLOOKUP searches for a value in one range and returns a related value from another range. It is usually the best modern lookup formula for new workbooks.
Syntax or pattern
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])5 practical examples
Find a product price
Look up the price for the product name in A2 from a product list.
=XLOOKUP(A2, Products[Product], Products[Price], "Not found")This searches the Product column and returns the matching Price.
Find an employee department
Return the department assigned to an employee ID.
=XLOOKUP(E2, Staff[ID], Staff[Department], "Check ID")This is useful when IDs are unique and names may repeat.
Return a value from the left
Look up a product code and return the product name from a column to the left.
=XLOOKUP(D2, Products[Code], Products[Product])Unlike VLOOKUP, XLOOKUP can return values from either side.
Use a friendly missing message
Show a clear message when the lookup item is not in the list.
=XLOOKUP(A2, Customers[Email], Customers[Name], "No customer found")A helpful not-found message makes reports easier to read.
Two-way lookup by row and column
Find the sales amount for a selected product and month.
=XLOOKUP(H2, Products[Product], XLOOKUP(H3, Sales[#Headers], Sales))Nested XLOOKUP can choose the column first and then return the matching row value.
Common mistakes to avoid
- Using lookup and return ranges with different sizes.
- Forgetting the not-found argument.
- Using approximate match when exact match is required.
Related Excel examples
FAQ
Is XLOOKUP better than VLOOKUP?
For most new workbooks, yes. XLOOKUP is more flexible and easier to maintain.
Can XLOOKUP return multiple columns?
Yes, if the return array includes multiple columns in supported Excel versions.
Here are some ideas for you
Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.
- Excel formula booksSee ideas
Practice formulas with structured examples you can keep beside your desk.
- Excel shortcut guidesSee ideas
Build speed with keyboard shortcuts for selection, formatting and navigation.
- Numeric keypadsSee ideas
Helpful if you enter many numbers on a laptop or compact keyboard.
- External monitorsSee ideas
Useful for viewing large worksheets, formulas and reference tables side by side.
- Desk notebooksSee ideas
Sketch formula logic, report ideas and table structures before building.
- Laptop standsSee ideas
Make long spreadsheet sessions more comfortable and ergonomic.
Some links in this section may be affiliate links. Choose only what is useful for your own work.