Forum Discussion
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 in the lookup_array (ps: all non-empty cells in the lookup_array are unique values, no duplicates)
- the formula returns the first match
My biggest challenge is doing an Index & Match based on a substring of the lookup_value that is contained anywhere in a 2-dimensional array. The formula below will match a substring of the lookup_value that matches BOTH Criteria 1 [column B] AND Criteria 2 [Column C]. This worked at the time, but now I would like to update it with XLOOKUP and also so that it matches based on ANY of the values from multiple columns, rather than matching exact two columns.
=INDEX(Sheet2[Category], MATCH(1,COUNTIFS($A2,"*"&Sheet2[Option1]&"*",$A2,"*"&Sheet2[Option2]&"*"),0))
Ideally, I would like to use the "name" of the lookup_array in the formula so that additional columns in the future can be added without having to change the formula. The correct formula will return "Fruit" for Sheet1:B2 and "Vegetable" for Sheet1:B6.
Answers by SergeiBaklan in previous post I think are so helpful, but I can't modify those answers enough to make it work for this particular case. Thank you everyone for your help.
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")
14 Replies
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
=INDEX($E$2:$E$6,MATCH(1,MMULT(--(ISNUMBER(SEARCH($F$2:$I$6,A2))),{1;1;1;1}),0))
Maybe with this formula as shown in the attached file.
- Thank you OliverScheurich the SEARCH function is more efficient for sure! For some reason the equation is not working for me, perhaps because I have more than 4 columns in my real dataset. I tried to add {1;1;1;1....1} as many columns as I want it to search but it returns back an error. It may also be because I have empty blank cells in that array. How can I have the SEARCH ignore empty cells in the array?
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")
