Course / Lookups

XLOOKUP

The modern lookup: exact match by default, any return column, and a friendly message when nothing matches. Intermediate · 5 min · XLOOKUP, if_not_found

XLOOKUP is the modern all-rounder. You tell it three things: what to find, where to look, and what to return.

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)

• It does an exact match by default, so there is no FALSE to forget
• The return column can sit anywhere, to the left or right of the search column
• The optional if_not_found argument replaces #N/A with a value of your choice

XLOOKUP works in Excel 2021 and later. In older versions, use INDEX and MATCH instead.

Steps

1. A reader has requested the books in F2:F6. In G2, look up the title of the first request: =XLOOKUP(F2, $A$2:$A$9, $B$2:$B$9). Both ranges are locked so you can copy the formula down next.

2. Copy the formula down to G6. One request is not in the catalogue, so add a fourth argument that shows Not in catalogue instead of #N/A.

3. In H2:H6, return the number of copies for each request. It is the same search with a different return range. Use 0 for if_not_found, since a missing book has no copies.

4. Now search the other way. In G10, find the Book ID for the title in G9. The ID column is to the left of the titles, which XLOOKUP handles without any fuss.

Remember

• =XLOOKUP(value, lookup_array, return_array) does an exact match by default.
• The 4th argument, if_not_found, replaces #N/A with your own text or number.
• The return range can be to the left or right of the search range.
• Both ranges must be the same size.

Previous: INDEX and MATCH · Next: Two-way lookups