INDEX / MATCH to return values from table fields

See attached sample. The yellow highlighted cells in the 2nd table are where I want a formula - presumably it'll be a CSE/array entered in column I and copied horizontally. I want to match the value in column H (city) with all the companies (column A) that have that city listed in Table1.

So in my example, Bangkok (H2), would have the following in I2:K2: Acme Inc (I2), Sterling Cooper (J2), and Umbrella Corp (K2).... with L2:N2 being blank.

Sample B.xlsx

As someone who does not "like" tables, i converted your tables to ranges ( I am sure you will get a Table solution):

in I2

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(($B$2:$B$9=$H2)+($C$2:$C$9=$H2)+($D$2:$D$9=$H2)+($E$2:$E$9=$H2)+($F$2:$F$9=$H2),ROW($A$2:$A$9)-ROW($A$2)+1,""),COLUMNS($A:A))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across and down.

I just made it converted it back to table references (with CSE), and it worked great! Thank you!

Formula:

Please Login or Register  to view this content.

Option is easier with the function of the AGGREGATE.

Formula:

Please Login or Register  to view this content.