XLOOKUP is a modern lookup function that can search one range and return a value from another range.

Syntax

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

Example

=XLOOKUP(E2,A2:A4,B2:B4,”Not Found”)

If E2 = E102:

Result: Neha

Salary:

=XLOOKUP(E2,A2:A4,C2:C4,”Not Found”)

Result: 50000

Advantages

• Can look left or right
• Does not require column numbers
• Supports an if_not_found argument
• Easier to maintain than many VLOOKUP formulas