How to lookup multiple values based on multiple criteria in one cell?
GT1992
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.
JvdV
One option:
Formula in C2:
=MAP(B2:B4,LAMBDA(a,TEXTJOIN(", ",,VLOOKUP(TEXTSPLIT(a,", "),F2:G6,2,0))))
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
TOP Ranking
- 1
Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)
- 2
Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background
- 3
pump.io port in URL
- 4
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 5
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 6
Spring Boot JPA PostgreSQL Web App - Internal Authentication Error
- 7
Emulator wrong screen resolution in Android Studio 1.3
- 8
3D Touch Peek Swipe Like Mail
- 9
Double spacing in rmarkdown pdf
- 10
Svchost high CPU from Microsoft.BingWeather app errors
- 11
How to how increase/decrease compared to adjacent cell
- 12
Using Response.Redirect with Friendly URLS in ASP.NET
- 13
java.lang.NullPointerException: Cannot read the array length because "<local3>" is null
- 14
BigQuery - concatenate ignoring NULL
- 15
How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?
- 16
ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3
- 17
Can a 32-bit antivirus program protect you from 64-bit threats
- 18
Make a B+ Tree concurrent thread safe
- 19
Bootstrap 5 Static Modal Still Closes when I Click Outside
- 20
Vector input in shiny R and then use it
- 21
Assembly definition can't resolve namespaces from external packages
Comments