Use XLOOKUP

The modern lookup: find a value in any column and return any other column — even to the left.

You will learn to

• Use lookup_array and return_array
• Add a friendly "not found" result

How it works

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) finds a value in one range and returns the matching item from another. It defaults to exact match, can look left, and lets you set a not-found message.

=XLOOKUP(G1,A2:A6,D2:D6,"Not found")