comma separated values - lookup on Google Sheets

[YvesV]

HI everyone. I have a dataset with comma separated values in two columns (B and C), the source and target language columns (see screenshot). I would want to do a lookup somehow, so that I can find anyone that has ENglish as a source language and Swedish as a target language. To give just one example. In column A I have the names (not shown here for privacy reasons), so I would want to get those names as the result. All names.

I've had two issue with different methods I tried:

- I either ended up with English as a source language result, only if it was the only language someone mastered. Not if they had multiple languages and English happened to be one of them.
- On another instance, I got the result, but it only showed me one.

But rather than fumbling around, there is maybe someone with an easy answer

Many thanks,


Yves

 

[Rick Rothstein]

EDIT NOTE: I just noticed this question was being asked for Google Sheets... I know nothing about Google Sheets so I do not know if it has a TEXTJOIN function nor if it even supports array formulas.

Give this array-entered** formula a try...

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("English",B1:B20))*ISNUMBER(SEARCH("Swedish",C1:C7)),A1:A20,""))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[jasonb75]

You could try the filter function, something on the basis of

=FormulaArray(FILTER(name list,ISNUMBER(SEARCH("English",source language list)),ISNUMBER(SEARCH("Swedish",target language list))))

 

[YvesV]

EDIT NOTE: I just noticed this question was being asked for Google Sheets... I know nothing about Google Sheets so I do not know if it has a TEXTJOIN function nor if it even supports array formulas.

Give this array-entered** formula a try...

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("English",B1:B20))*ISNUMBER(SEARCH("Swedish",C1:C7)),A1:A20,""))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Hi Rick, it did not work as intended indeed. But thanks for the effort anyway!

 

[YvesV]

You could try the filter function, something on the basis of

=FormulaArray(FILTER(name list,ISNUMBER(SEARCH("English",source language list)),ISNUMBER(SEARCH("Swedish",target language list))))

This does work! Thanks a lot, that's awesome.

I do have a follow-up question, if I may, not sure if that's possible.
Can you make the "source language list" and "target language list" link to some sort of dropdown data validation or something else, so you can change it depending on the source/target language you need, so yet get different results? Without having to manually change the languages each time in the formula, to get a different result.

 

[YvesV]

This does work! Thanks a lot, that's awesome.

I do have a follow-up question, if I may, not sure if that's possible.
Can you make the "source language list" and "target language list" link to some sort of dropdown data validation or something else, so you can change it depending on the source/target language you need, so yet get different results? Without having to manually change the languages each time in the formula, to get a different result.

Sorry, link the actual language "English" and "Swedish" in your formula, not the language list... my bad.

 

[jasonb75]

I used text strings as an example, you can use cell references instead.

I don't know if google sheets has data validation the same as excel, but if it does then it should work with that just fine.