XLOOKUP is a function which looks for a match from another table which will return a single result or range by row.
This is a very powerful and useful formula. Frankly, I am not a fan of this function (yet). First of all, it is not available in older version of Excel, so if you are sending it to a client with older version of excel (2019 and earlier versions), they won't be able to see what you are trying to do. And second, you will need to be very specific as to what you are using it for, otherwise you will get inconclusive result (see image).
Now that I am over with my ranting, below is the formula set-up. I've divided it into two categories. Simple (low chances of messing it up) and Complex (you can mess it up so easily if you are not paying real good attention). I am basically just sharing the 4 possible ways you can write your formula. I hope it helps, if it's still too confusing I've added a link at the end of this blog that will show a video and highlight more examples.
Simple:
=XLOOKUP(lookup_value, lookup_array, return_array)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
lookup_value = item you want to lookup for
lookup_array = column where the item with similar "values" is located
return_array = column(s) where the result is located (If single column, it does not matter if the return_array is left or right hand side of the lookup_array. If multiple column, it has to be the right hand side columns)
[if_not_found] = value which you want to show up if the item is not found
Complex:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
[match_mode]
0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.
1 - Exact match. If none found, return the next larger item.
[search_mode]
1 - Perform a search starting at the first item. This is the default.
-1 - Perform a reverse search starting at the last item.
2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Here's the reference with samples and videos, I hope this helps!
Comments