XLOOKUP

Finds a value in one range and returns the matching value from another — in any direction.

Syntax

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

Lookup & Reference. Available from Excel 2021.

Practise XLOOKUP6 interactive exercises, free, no account needed

Need the formula for your own sheet? Use the XLOOKUP Formula Generator

=XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6,"Unassigned")

Returns West

Looks up the customer id and returns the region, falling back to a readable message when nothing matches.

Leaving the ranges relative. It works on the first row, then shifts when filled down and starts returning #N/A for the earliest records.

Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.

The value is right, but this exercise is about the lookup. Typing the answer for three rows is feasible; for three thousand it is not, which is why the function exists.Look up a customer's region with XLOOKUP
This returns the right answer for row 2, but the ranges are relative. Fill it down to row 3 and they shift to F3:F7 and G3:G7 — the first customer drops out of the search and you get #N/A. Add $ to freeze them.Lock ranges so a lookup survives fill-down
CUST-999 is genuinely not in the table, so this returns #N/A. That is technically correct but unreadable in a report. XLOOKUP takes a fourth argument for exactly this case.Handle missing lookups gracefully
That gets you as far as the customer name. You have done the first hop but not the second — the terms live in the other table.Chain two lookups with XLOOKUP

Knowing what a function does and being able to write it from memory are different skills. These close the gap.