X Lookup vs Index Match

Copper Contributor

Is there a situation in which X Lookup or Index Match are better than the other?

1 Reply

@MBelcher7 The biggest benefit that XLOOKUP has over INDEX / MATCH (or, more accurately, over INDEX / XMATCH) is the simplified syntax and the optional [if_not_found] argument. XLOOKUP is an all-in-one function, whereas IFNA / INDEX / XMATCH requires 3 functions to achieve the same results:

 

=XLOOKUP(G2, A2:A10, B2:B10, "no match")

=IFNA(INDEX(B2:B10, XMATCH(G2, A2:A10)), "no match")

 

Both are capable of performing a 2-way lookup, with XLOOKUP being slightly shorter:

 

=XLOOKUP(G2, A2:A10, XLOOKUP(H1, B1:E1, B2:E10))

=INDEX(B2:E10, XMATCH(G2, A2:A10), XMATCH(H1, B1:E1))

 

The biggest drawback, however, is that XLOOKUP cannot output a 2D set of results when multiple lookup_values are specified. For example:

 

=XLOOKUP(G2:G5, A2:A10, B2:E10)

 

...will only return the first column (B) of the return_array (B2:E10), even though 4 columns were specified.

 

XLOOKUP ExampleXLOOKUP Example

 

INDEX / XMATCH is perfectly capable of handling this, though, by specifying the desired column numbers to be returned:

 

=INDEX(B2:E10, XMATCH(G2:G5, A2:A10), {1,2,3,4})

 

INDEX / XMATCH ExampleINDEX / XMATCH Example

 

Those are the biggest differences that I'm aware of.