I have two tables in Google Sheets.

Table 1 is a list of contacts arranged vertically with multiple records appearing for each city.

In Table 2, I'd like the city name to appear only once and the contacts to be displayed horizontally.

My first guest was to use Index Match Match, however I'm unable to retrieve contacts 2 and 3 - an error appears saying Function INDEX parameter 3 value 2 is out of range.

Any ideas on how to solve this problem and is Index Match Match the best method?

CodePudding user response：

Your issue is happening because you are using a MATCH() function in the column parameter of the INDEX() function. MATCH() returns a row index of where the value is found, not a column.
While it is possible to solve with an INDEX/MATCH method, another way you could solve this is with VLOOKUP:

=ArrayFormula(IFERROR(vlookup($A$12:$A$14,if($B$3:$B$8=B$11, {$A$3:$A$8,$C$3:$C$8}, false),2,FALSE)))

This formula can be placed in cell B12 and copied across the columns.