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:$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")- After using OliverScheurich formula for a while I saw two inefficiencies that I was able to address using JMB17 suggestions above.
Inefficiency #1 was the "ROW(1:11)^0" which I changed to TRANSPOSE(COLUMN($F$2:$I$2). The row option made is such that each row had a different number, for example row 3 would have the formula above translated to "ROW(3:13)" and when these rows would be reordered (maybe ordered A-Z vs Z-A for different reasons), al the formulas were highlighted as inconsistent. This could hide a real error message, and also it was very inefficient. Now that they are frozen to a specific 15 columns, I also do not have to worry about accidently deleting those columns and creating another error.
Inefficiency #2: Not using the "IFERROR" function. When I added new data into the table that had not been indexed yet, the result could not return a match and would return an N/A which other mathematical functions in other tables could not translate and those tables were then broken. Using the IFERROR function allows me to flag those that do not match, while also not breaking other formulas that are depending on the match results.
- I checked again, and the formula provided by OliverScheurich works when there is NO EMPTY cells within the search array. My search array does have empty cells, so I filled them with a value that would not result in a false positive (is unlikely to be found in my search list). This is a great short-term fix. If there is a workaround filling empty cells, I would be happy to learn more.
=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.