How do I use XLOOKUP?

XLOOKUP finds a value in one range and returns a matching value from another range, in either direction, with an exact match by default. Use =XLOOKUP(lookup_value, lookup_array, return_array) and it replaces most VLOOKUP and INDEX/MATCH formulas with one simpler function.

Formula (Excel and Google Sheets (a newer function; if it is unavailable in your version, INDEX/MATCH covers the same cases))

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")

XLOOKUP asks for three things in plain order: what are you looking for, where do you search for it, and what do you want back. Unlike VLOOKUP, the return range does not have to be to the right of the search range, and unlike both VLOOKUP and INDEX/MATCH, you can add a fourth argument for what to show if nothing matches, instead of getting a bare #N/A.

The example above returns "Not found" instead of an error when A2 has no match in Sheet2!A:A, which keeps a sheet readable when some lookups are expected to miss.

Step by step

1
State what you are searching for
This is your lookup value, usually a cell reference.
2
State where to search
This is the range that contains that value, e.g. Sheet2!A:A.
3
State what to return
This is the range holding the value you actually want, e.g. Sheet2!C:C.
4
Optionally handle no match
Add a fourth argument, in quotes, for what to show if nothing is found, instead of an error.

Common questions

Does XLOOKUP need an exact match flag like VLOOKUP's FALSE?

No. XLOOKUP defaults to an exact match, which is the safer default VLOOKUP never had.

Can XLOOKUP replace INDEX/MATCH?

In Excel versions that support it, yes, for almost every case. It reads more clearly and handles missing matches more gracefully.

Related formulas

How to Use VLOOKUP in Excel and Google Sheets

How to Use INDEX and MATCH Together in Excel and Google Sheets

How to Calculate Percentage Change Between Two Numbers

From the blog

How to Make Sense of Your Spreadsheet Data Without Being an Analyst

Want a formula written for your exact spreadsheet, not a generic example?

Try the free formula generator