CODEWITHSUNDEEP
google-sheetsmatcharray-formulas
Morphan
Morphan

Reputation: 41

Google Sheets: How to get values of first column for multiple matches of a search string in the other columns?

I have the following table with names in column A and cities in columns B to E:

enter image description here

Cities can occur as single entries or as combinations (i.e. "Dublin" or "Dublin, San Francisco"). City combinations are always sorted alphabetically, therefore a combination like "Tel Aviv, Dublin" cannot occur.

I would like to get all names for a given city combination. For each city combination I'd like to get a sorted list of the names with one name per cell:

enter image description here

For example, the combination "Amman, Tel Aviv" occurs four times in the given data (highlighted in the image), therefore in the resulting table there should be four names in the row of "Amman, Tel Aviv".

Is there a way using formulas (not scripts)? I know that HLOOKUP/VLOOKUP will only return one value. I've thought of maybe some combination of ARRAYFORMULA and FILTER or MATCH, but I barely understand how ARRAYFORMULA works.

Link to example sheet.

Upvotes: 1

Views: 963

Answers (2)

basic
basic

Reputation: 11968

Try FILTER function:

=TRANSPOSE(FILTER($A$3:$A$15;MMULT(--($B$3:$E$15=A20);SEQUENCE(4;1;1;0))=1))

The first argument 4 of the SEQUENCE function must match the number of columns in range $B$3:$E$15

enter image description here

Upvotes: 2

Kevin
Kevin

Reputation: 2631

You can do this with a formula like this (I'm assuming you have named ranges for the names and cities) after entering the formula press Crtl+Shift+Enter. This will make it and array formula, then copy it to all other columns:

=(IFERROR(INDEX(names;SMALL(IF(cities=$A20;ROW(names)-MIN(ROW(names))+1);COLUMNS($A$20:A20)));"")

If you want a breakdown of exactly what is happening see this article. Extract multiple matches into separate columns

Upvotes: 2

Related Questions