Not understanding XLOOKUP arguments as they apply here

Platinum Contributor

Apr 05, 2026

Golden rule:

lookup_value = what you know (typed in a cell)
lookup_array = where that value exists (single column/row in your data)
return_array = where the answer is (same size as lookup_array, different column/row)

XLOOKUP syntax

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

Argument	What it means
lookup_value	What you are searching for (e.g., a product ID, name, or number)
lookup_array	The column/row where Excel should look for that value
return_array	The column/row with the value you want to return (same size as lookup_array)
[if_not_found]	Optional: What to show if no match is found (e.g., "Not found")
[match_mode]	Optional: 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
[search_mode]	Optional: 1 = search first to last (default), -1 = search last to first, etc.

Often mix up lookup_array and return_array.

lookup_array is where the lookup_value lives.
return_array is where the answer lives.
Both must be the same shape (same number of rows for vertical lookup, same number of columns for horizontal lookup).

Example misunderstanding:
People think “lookup_array” means the whole table, but it’s just the column/row containing the value you’re matching.

My answers are voluntary and without guarantee!

Hope this will help you.

Forum Discussion