The Vlookup vs. Index Match debate has been going on for a while. Vlookup is easier to grasp and often all you really need. Index/Match can search right-to-left or left-to-right and doesn’t require you select as large an array in most cases. No matter what side of the fence you’re on with that debate, XLOOKUP seems to have outdone them BOTH.

XLOOKUP vs. INDEX & MATCH

In this data set we’re looking at a set of SKUs from an ecommerce store, along with the number of sales for each. In column G we’ve created a formula that searches for the SKU in column B and pulls out the corresponding number of sales

Breaking Down the MATCH function

The MATCH function here searches for S-001 within the array in column B. The ‘1’ returned by the function is the row number that result matches in the array (not the row number within the workbook). In other words, although the title of our column ‘SKU’ is actually in row 1 of column B, the SKU ‘S-001’ is in row 1 of the array we selected.

Combining MATCH with INDEX

The INDEX function takes two mandatory arguments – the array and the row number in the array. By inputting our sales array (D2:D11) and wrapping the INDEX function around the match function, the total Sales of 22 are returned because ’22’ is in the first row of the array.

After doing this a few times, it’s very easy to get the hang of INDEX+MATCH for this type of situation. Let’s see how XLOOKUP handles the same problem…

XLOOKUP in Place of INDEX & MATCH

Right below our INDEX & MATCH formula, I’ve input an XLOOKUP formula that attempts to find the same information. In this case, it’s a clear winner.

It takes 3 mandatory arguments:

1. Value
2. Lookup Array (where to look for the value)
3. Return Array (what values to return)

In this case it does all the work of INDEX/MATCH with less effort. But that’s not where the benefits of XLOOKUP stop. Let’s look at the other ‘optional’ arguments contained within XLOOKUP.

XLOOKUP’s Optional Arguments

• if_not_found – This is a big step forward in my opinion. This option is essentially like wrapping the entire function inside an ‘iferror’ statement
• match_mode – This allows you to set exact match, approximate, or even WILDCARD matches! By default it’s set to exact match.
• search_mode – This allows you to search an array top-to-bottom or bottom-to-top. For some people with data organized in a very ordered way, this could be exactly what you need.

Let’s take a look at the first additional feature ‘if_not_found’ and compare against Index/match:

In this example, I’ve added the text string “Not Found” to appear if the value in F2 is not found in our array. Since ‘S-012’ is not one of the SKUs in the array, the text is printed! Very easy.

Let’s get the same result using our Index/Match function:

To do the same thing with these functions, we have to wrap the whole thing in an IFERROR function with the text string “Not Found” showing up if there is an error. Pretty cool that XLOOKUP has this feature built right in to it.

I’m still learning about XLOOKUP like everyone else, but so far I’ve very impressed.