Summary

XLOOKUP searches a range for a value and returns a corresponding result from another column or row. It is the lookup function to reach for first in any workbook that supports it. This article explains the syntax, works through practical examples, and settles the choice between XLOOKUP, VLOOKUP, and INDEX with MATCH.

Before You Start

• Excel for Microsoft 365, Excel 2024, or Excel 2021 — on Windows or Mac — or Excel on the web. Microsoft's documentation states that XLOOKUP is not available in Excel 2016 or Excel 2019.
• A short list of data to work on: one column holding the values you will search, and another holding the answers you want back.

Instructions

1. Understand the XLOOKUP Syntax

XLOOKUP takes three required arguments and three optional ones, and knowing what each position controls is what makes every formula below readable.

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

Argument	Description	Required
lookup_value	The value to search for	Yes
lookup_array	The column or row to search in	Yes
return_array	The column or row to return results from	Yes
if_not_found	Text to display if no match is found	No
match_mode	0 = exact match, return #N/A if none is found (default); -1 = exact match, or the next smaller item; 1 = exact match, or the next larger item; 2 = wildcard match, where *, ? and ~ have special meaning	No
search_mode	1 = search from the first item (default); -1 = search backwards from the last item; 2 = binary search on a list sorted ascending; -2 = binary search on a list sorted descending	No

The three optional arguments are what most of this article is about. if_not_found is the reason XLOOKUP does not need an IFERROR wrapper, and match_mode defaulting to an exact match is the reason it does not quietly return the wrong row.

2. Basic Example — Look Up a Price

Find the price of a product by name.

1. Assume Column A contains product names and Column B contains prices.
2. In an empty cell, type:

   =XLOOKUP("Widget", A2:A100, B2:B100, "Not found")

3. Press Enter. The formula returns the price for "Widget", or "Not found" if it does not exist.

3. Replace a VLOOKUP with XLOOKUP

Rewriting an existing VLOOKUP as an XLOOKUP removes the column-index number, which is the argument that silently breaks when someone inserts a column.

VLOOKUP example:

=IFERROR(VLOOKUP("Widget", A2:C100, 3, FALSE), "Not found")

XLOOKUP equivalent:

=XLOOKUP("Widget", A2:A100, C2:C100, "Not found")

4. Use XLOOKUP with Multiple Return Columns

Point the return argument at several columns at once and a single formula returns a whole row of matching data.

1. In an empty cell, type:

   =XLOOKUP("Widget", A2:A100, B2:D100, "Not found")

2. Press Enter. Values from columns B, C, and D for the matching row appear across three cells, filled by that one formula.
3. If the cell instead shows #SPILL!, one of the cells the results need is already occupied. Clear or move whatever is in the way and the values appear — see How to Fix Common Excel Formula Errors.

5. Choose the Right Lookup for the Job

XLOOKUP is the right default in any workbook that supports it, and there are two honest reasons to reach for something else.

What XLOOKUP fixes about VLOOKUP

• No column counting. VLOOKUP asks for the number of the column to return, counted from the left edge of the range you gave it. Miscount by one and it returns the wrong field without complaint. XLOOKUP asks you to select the return column directly, so there is no number to get wrong and nothing to update when someone inserts a column into the source data.
• It searches in both directions. VLOOKUP only ever returns something to the right of the column it searched. XLOOKUP takes the search column and the return column as two separate arguments, so the answer can sit to the left, to the right, or on another sheet entirely.
• It has a place to put the "not found" message. VLOOKUP needs an IFERROR wrapper around the whole formula, which also swallows genuine mistakes. XLOOKUP's fourth argument handles a missing match on its own and leaves every other error visible where you can see it.
• Exact match is the default. VLOOKUP matches approximately unless you remember to type FALSE as its fourth argument, and an approximate match on unsorted data returns plausible wrong answers in silence. XLOOKUP's match_mode defaults to an exact match.

When VLOOKUP is still the answer

• The workbook is not yours. A colleague's VLOOKUP that returns correct numbers is not a defect. Rewriting formulas in someone else's file to modernize them earns you the blame for the next thing that breaks in it, and buys nothing the file needed.
• Someone opening the file is on Excel 2016 or Excel 2019. XLOOKUP does not exist in those versions, so it cannot run there. Step 6 covers what those readers actually see and how to check before you send.

Where INDEX and MATCH still earn their place

INDEX and MATCH are two older functions used as a pair. MATCH finds which row holds your value, and INDEX returns whatever sits in that position of another column:

=INDEX($D$2:$D$500, MATCH($G2, $A$2:$A$500, 0))

That pair searches in any direction and never counts column positions, so it solves the same two problems XLOOKUP solves — and it works in every version of Excel, including the ones without XLOOKUP. That is why it survives. The cost is readability: it is two nested functions instead of one, and the person who inherits the sheet has to unpick both. Reach for it when the file has to open on Excel 2016 or 2019 and you still need XLOOKUP's flexibility.

The decision in one table

Your situation	Use
New workbook, and everyone who opens it is on Microsoft 365, Excel 2024, or Excel 2021	XLOOKUP
Editing someone else's workbook where the VLOOKUP already returns correct numbers	VLOOKUP — leave it alone
The file will be opened by people on Excel 2016 or Excel 2019	VLOOKUP, or INDEX with MATCH
The answer sits to the left of the column you are searching	XLOOKUP, or INDEX with MATCH on older Excel
Columns get inserted into the source data regularly	XLOOKUP, or INDEX with MATCH — neither counts column positions
You need the last matching row rather than the first	XLOOKUP with search_mode set to -1
The lookup value falls inside a band, such as a tax bracket or a postage weight	XLOOKUP with match_mode set to -1, or VLOOKUP with TRUE

Full walkthrough of the older function: How to Use VLOOKUP in Excel.

6. Check Before Sharing the Workbook

Confirm which version of Excel everyone opening the file runs, because Excel 2016 and Excel 2019 have no XLOOKUP and every such formula returns #NAME? there.

Here is what that looks like on their screen. The formula bar may show the prefix _xlfn. in front of the function name — =_xlfn.XLOOKUP(...) — and they see a red error where the price should be, with a formula they cannot act on. Nothing tells them the file was built somewhere else.

To find out before you send it:

1. Ask whoever will open the file which Excel they run — File > Account, under Product Information, tells them. Excel 2016 and Excel 2019 are the two that lack XLOOKUP.
2. If either is on the list, rewrite those formulas as VLOOKUP, or as INDEX with MATCH.
3. The Compatibility Checker (File > Info > Check for Issues > Check Compatibility) is worth a look as a second pass, but read its result carefully: Microsoft documents it as checking against Excel 2013, 2010 and 2007, so it does not report on Excel 2016 or Excel 2019 specifically.

Tip: If you do not know what everyone is running, the safe default for a file leaving your team is INDEX with MATCH. It behaves like XLOOKUP and runs everywhere.

Troubleshooting

INFO

XLOOKUP is available in Excel for Microsoft 365, Excel 2024, and Excel 2021 — on Windows and Mac — and in Excel on the web. Microsoft states that it is not available in Excel 2016 or Excel 2019. Share a workbook with someone on one of those versions and every XLOOKUP formula returns #NAME? on their screen.

Symptom / Error	Potential Cause	Solution
#NAME? error	The Excel opening the file has no XLOOKUP	Check the version under File > Account. Excel 2016 and Excel 2019 never shipped the function. Rebuild the formula as VLOOKUP or as INDEX with MATCH — see Step 5.
#NAME? and the formula bar reads _xlfn.XLOOKUP	Workbook was authored in a newer Excel	Same cause as above, seen from the other side. Rebuild the formula with a function this version has; retyping XLOOKUP on its own will not help.
#N/A error	No matching value found	Check spelling and data types — a code stored as text on one sheet and as a number on the other never matches. Add an if_not_found argument to show readable text.
#SPILL! error	Cells beside the formula are occupied	A multi-column XLOOKUP needs empty cells to write into. Click the cell, then use Select Obstructing Cells in the warning menu to jump to what is in the way.
Wrong result returned	Duplicate values in the lookup column	XLOOKUP returns the first match. Sort the data, or set search_mode to -1 to search backwards from the last row.
Result is close but not right	match_mode set to -1 or 1	Those modes fall back to the next smaller or next larger item when nothing matches exactly. Remove the argument to return to the exact-match default.

Related Articles

• How to Use VLOOKUP in Excel
• How to Fix Common Excel Formula Errors
• Understanding Absolute and Relative Cell References in Excel
• How to Create a PivotTable in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?