XLOOKUP Multiple Criteria

Are you still using INDEX and MATCH function when you want a two way lookup and looking for a simpler solution like XLOOKUP Multiple Criteria, then this blog is for you.

Reading Time – 2 Minutes

What is XLOOKUP()

X means ability to look for a value in any directions (vertically, horizontally, left and right). This function overcomes the issues we face in VLOOKUP() and INDEX() & MATCH() and hence it can replace both VLOOKUP, INDEX() and MATCH().

XLOOKUP() Syntax

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

Understand Structure

lookup_value = what to search

lookup_array = In which column to search

return_array = If value found, then from which column you want value

[if_not_found] = If not found, then what you want in return

[match_mode] = It impacts what value is returned, you can enter 0, -1, 1 and 2

• 0 – Exact match and If none found, return #N/A. This is the default.
• -1 – Exact match and If none found, return the next smaller item.
• 1 – Exact match and If none found, return the next larger item.
• 2 – A wildcard match where *, ?, and ~ have 

[search_mode] = Now, it is turn to use God mode, the xlookup search mode. In this you define four options to start looking value from top or from bottom.

• 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.

How to use simple and two way XLOOKUP

How to use XLOOKUP search mode

If you have duplicate entries like in this case “Laptop” and want to get Sales values from Top and fom Bottom then you can use XLOOKUP search mode as shown