Forum Discussion
Not understanding XLOOKUP arguments as they apply here
3 Replies
It looks like repeating of
https://techcommunity.microsoft.com/discussions/excelgeneral/how-xlookup-arguments-apply-in-this-case/4508574
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.
HelloNikolinoDE
That is a very nice write up about the XLOOKUP. I am sure many community members would learn from this write up.
For match_mode I at times see FALSE. Is that the same as 0?
