Forum Discussion
Index & Match based on a substring of the look up value contained in a 2-dimension 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")
=INDEX($E$2:$E$6,MATCH(1,MMULT(--(ISNUMBER(SEARCH($F$2:$P$6,A2)))*NOT(ISBLANK($F$2:$P$6)),{1;1;1;1;1;1;1;1;1;1;1}),0))
This formula takes into account empty cells and works for 11 options in my sheet. Unfortunately, when I made my first suggestion, I didn't consider the empty cells.
=INDEX($E$2:$E$6,MATCH(1,--(MMULT(--(ISNUMBER(SEARCH($F$2:$P$6,A2))*NOT(ISBLANK($F$2:$P$6))),ROW(1:11)^0)>0),0))
You can try this formula which works for multiple matches in my spreadsheet. The expression ROW(1:11)^0 translates to {1;1;1;1;1;1;1;1;1;1;1} and can easily be adapted to other data ranges. The number of rows (11 in this example) must match the number of options which is the number of columns from F:P in this example. The number of columns must be entered within the expression $F$2:$P$6 in both the SEARCH and ISBLANK function.