I have a spreadsheet where I have used SEARCH() to get the possible linked ingredients from a match in a string. This sometimes leaves me with multiple possible matches.
Now I would like to lookup the translated words of these possible matches using an INDEX MATCH. Except I cannot as cells have multiple values and therefore multiple criteria.
My question is: how can I lookup multiple values based on multiple criteria and have them in one cell?
An example as better explanation:
The table I have:
| description | productNameEN | productNameIS |
|---|---|---|
| Red onion | Onion, Red onion | |
| Egg yolk | Egg, Egg yolk | |
| Lemon | Lemon |
And then I would like to fill the productNameIS column with the translations from another table, so that it looks like this:
| description | productNameEN | productNameIS |
|---|---|---|
| Red onion | Onion, Red onion | Laukur, Rauðlaukur |
| Egg yolk | Egg, Egg yolk | Egg, Eggjarauða |
| Lemon | Lemon | Sítronu |
This is a table example of the translations.
| EN | IS |
|---|---|
| Egg | Egg |
| Egg yolk | Eggjarauða |
| Lemon | Sítronu |
| Onion | Laukur |
| Red onion | Rauðlaukur |
Now the INDEX MATCH works for the word lemon as this is singular, but not for the other cells. I need to keep the multiple values in one cell for further use in my spreadsheet.
CodePudding user response：
One option:
Formula in C2:
=MAP(B2:B4,LAMBDA(a,TEXTJOIN(", ",,VLOOKUP(TEXTSPLIT(a,", "),F2:G6,2,0))))
CodePudding user response：
You may try SEARCH() with FILTER() then TEXTJOIN().
=TEXTJOIN(", ",TRUE,FILTER($I$2:$I$6,ISNUMBER(SEARCH($H$2:$H$6,B2))))
For dynamic spill array try-
=BYROW(B2:B4,LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER($I$2:$I$6,ISNUMBER(SEARCH($H$2:$H$6,x))))))
