The Power of XLOOKUP

XLOOKUP is the modern successor to VLOOKUP, offering a more flexible and robust way to search for data. Unlike VLOOKUP, it defaults to exact matches and can search both vertical and horizontal ranges without caring about column order.

Syntax

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

Key Advantages

1. Bi-directional: Can look left or right, up or down.
2. No Column Counting: You select the return range directly, so inserting columns won’t break your formula.
3. Built-in Error Handling: The [if_not_found] argument lets you specify a custom message like “Not Found” without wrapping it in formatting IFERROR.

Example

To find an email (col B) based on an ID (col A):

=XLOOKUP(G2, A:A, B:B, "No match found")