Forum Discussion

2 Replies

  • A good question might be 'Why don't you just use XLOOKUP?'

    Something that INDEX/XMATCH will do that XLOOKUP is reluctant to do is return and array of arrays

    = LET(
        fieldNum,  XMATCH(outputHeaders, TblHeaders),
        recordNum, XMATCH(foreignKeys, primaryKeys),
        INDEX(data, recordNum, fieldNum)
      )

     

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    Yes.  Actually XLOOKUP was a welcome addition to replace the traditional INDEX( MATCH() ) combination that was previously required in certain cases.  In addition to replacing the need for the INDEX - MATCH combination, XLOOKUP also has IFERROR (technically better since it is specifically IF_NOT_FOUND and therefore you don't accidentally mask errors for other things), and new and different search capabilities and is more efficient.

    But all that said, yes, you can use

    =IFERROR( INDEX( output_array, MATCH( search_item, search_array, limited_search_options) ), if_not_found)

    as a close but still not as good as XLOOKUP option

    of course you still have other LOOKUP options including LOOKUP, VLOOKUP, and HLOOKUP.  In some cases these have 'simplier' entry but still lack a number of features and benefits of XLOOKUP.  For example you have VLOOKUP( value, table, index) so something like =VLOOKUP( a1, b1:e1000, 4) instead of =XLOOKUP( a1, b1:b1000, e1:e1000) which is slightly easier to type (7 characters in this case) but having the ability to include forward, backward, wildcard search and if_not_found value usually outweighs those 7 characters.

    and this doesn't even get into adding in named ranges and structured table references that will work better in XLOOKUP to improve readability:

    =XLOOKUP ( name, MyTable[Names], MyTable[eMail] )

    this is pretty clearly a lookup for 'name' in MyTable and returning the corresponding e-mail

    the equivalent in VLOOKUP might be:

    =VLOOKUP ( name, MyTable, 4 )

    and we can see it will look up 'name' in MyTable and return the 4th column, whatever that is.

Resources