Forum Discussion
Not understanding XLOOKUP arguments as they apply here
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?
Yes — in practice, FALSE behaves the same as 0 for match_mode in XLOOKUP, meaning exact match only.
When you write:
=XLOOKUP(A2, B2:B10, C2:C10, , FALSE)
Excel interprets FALSE as 0, which means:
Exact match (default behavior).
Even though it works, FALSE is not officially documented for match_mode in XLOOKUP.
- In older functions like VLOOKUP, FALSE explicitly meant exact match.
- In XLOOKUP, Microsoft defines:
0 → exact match (default)
-1, 1, 2 → other match types
Use 0 instead of FALSE for clarity and consistency:
=XLOOKUP(A2, B2:B10, C2:C10, , 0).
If someone accidentally wrote match_mode = TRUE, that would be 1, which changes behavior entirely (exact or next larger). So FALSE is “safe” because it equals 0, but TRUE would be dangerous. That’s another reason to stick with 0.
My answers are voluntary and without guarantee!
Hope this will help you.
