LOOKUPS

XLOOKUP

Find a key in one range and return a related value from another range.

After this lessonReplace many VLOOKUP use cases with a clearer exact lookup.

How it works

XLOOKUP searches the lookup array and returns the value at the same position from the return array. Exact matching is the default, which is safer for IDs and material numbers.

Use a useful if_not_found value when missing master data is an expected business case.

REAL WORK

Return supplier, description, price or storage location from a master table using SKU or material number.

SYNTAX

The pattern

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

Example

=XLOOKUP("SKU-104",A2:A6,C2:C6)

TRY IT

Test the formula

The sample sheet is local to this page. Change the formula and see the result immediately.

Result

QUICK CHECK

Does XLOOKUP require the return column to be to the right?

Progress is stored only in this browser. No account required.