How to return multiple results in the same cell using Index, Match and Search
I have a working Index, Match, Search formula that searches the titles of my products for specific terms that I've determined in a Key, if no result is found then alternatively it searches my product SKU's using a different key.
Currently, the formula is only giving me the first result that it finds within my key but I would like it to return as many results as possible all separated by Pipes or commas.
Here's the working formula that is giving me 1 result.
=IF((ARRAYFORMULA(INDEX('Array Key'!$J:$J,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$I:$I,$B2)),0))))="",(ARRAYFORMULA(INDEX('Array Key'!$L:$L,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$K:$K,$A2)),0)))),(ARRAYFORMULA(INDEX('Array Key'!$J:$J,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$I:$I,$B2)),0)))))
This would be awesome if I could get it working. Here's a link to a copy of my spreadsheet if anyone would like to see it working. Feel free to make any edits.
https://docs.google.com/spreadsheets/d/1APLCVnqmP51UbimyZeY-S0BoSEuFWkPa2LtAxVX9H0E/edit?usp=sharing
I've tried to use the following formula but it doesn't quite work how I'd like it to.
=IF((ARRAYFORMULA(INDEX('Array Key'!$F:$F,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$E:$E,$B2)),0))))="",(ARRAYFORMULA(INDEX('Array Key'!$H:$H,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$G:$G,$A2)),0)))),(ARRAYFORMULA(INDEX('Array Key'!$F:$F,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$E:$E,$B2)),0)))))&" | "&(ARRAYFORMULA(INDEX('Array Key'!$H:$H,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$G:$G,$A2)),0))))
This formula searches the 2nd key again and concatenates any results. However, I'm getting duplicates and it still only returns the first result found.
if this would be like:
then D2 cell would be:
=ARRAYFORMULA(TEXTJOIN(" | ", 1, UNIQUE({
IFERROR(VLOOKUP(TRANSPOSE(IFERROR(REGEXEXTRACT(SPLIT(B2, " "),
TEXTJOIN("|", 1, 'Array Key'!E$3:E)))), 'Array Key'!E$3:F, 2, 0));
IFERROR(VLOOKUP(TRANSPOSE(IFERROR(REGEXEXTRACT(SPLIT(A2, " "),
TEXTJOIN("|", 1, 'Array Key'!G$3:G)))), 'Array Key'!G$3:H, 2, 0))})))
