Forum Discussion

UI_c2022's avatar
UI_c2022
Copper Contributor
Jan 25, 2022
Solved

Index & Match based on a substring of the look up value contained in a 2-dimension array

Is it possible to update my formula below using XLOOKUP, such that any substring of the lookup_value (Sheet1:A2) can be matched across the lookup_array (Sheet2:B-J) the formula ignores empty cells...
excel
Formulas and Functions
  • JMB17's avatar
    JMB17
    Jan 26, 2022

    If I may, a few suggestions to modifying the formula:
    1) change Search(F2:I6, A2) to Search(A2, F2:I6). I believe blank cells in your data will match to A2 and could return incorrect results, but it won't match in the other direction.
    2) Instead of hardcoding the array of 1's, you could use TRANSPOSE(COLUMN($F$2:$I$2)) - if you insert columns, the formula should update.
    3) Try --(MMULT(...)>0). If you had "red apple" and "green apple" in the same row, mmult would return 2 for that row (searching for "apple"), causing match to fail (as match is looking for "1"). Also, using column() in #2 instead of an array of 1's makes this necessary.
    4) You could wrap the formula with Iferror for instances where nothing matches.

    Note you will need to use Ctrl+Shift+Enter if you don't have office 365 (because of the transpose function):
    =IFERROR(INDEX($E$2:$E$6,MATCH(1,--(MMULT(--(ISNUMBER(SEARCH(A2,$F$2:$I$6))),TRANSPOSE(COLUMN($F$2:$I$2)))>0),0)), "No Match")

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 26, 2022

UI_c2022 

As variant ant to minimize hardcoding:

If name with some gap category and options in Sheet2 (without headers) as range

here

in B2 it could be

=LET(
   category, INDEX( range,,1 ),
   data,     INDEX( range,
                    SEQUENCE( ROWS( range ) ),
                    SEQUENCE(, COLUMNS( range )-1,2 ) ),
  matching, --ISNUMBER( SEARCH( IF( data= "", UNICHAR(9999), data ), A2) ),
  id,         SUM( SEQUENCE( ROWS( data ) ) *
                   MMULT( matching,
                          SEQUENCE( COLUMNS( data ),,1,0 ) ) ),
IFS( A2 = "", "", id = 0, "not found", 1, INDEX( category, id ) ) )

and drag it down

Resources