Forum Discussion

1 Reply

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 04, 2024

    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 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 Example

     

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

Resources