How to summarize data WITHOUT using Pivottables?

[Glow85]

Hello! I have created a matrix that assigns specific sales reps to certain cities. I need to have this information drawn from Table 1.1 (See below) and re-organized by city. For some reason the pivottable does not seem to update when a change is made to a sales rep and his city. In addition, the pivottables are only able to summarize each city in rows, not across in columns. I need this data horizontally so that I may later use the vlookup function.


(Table 1.1)

B C D E F G
Sales Rep City 1 City 2 City 3 City 4 City 5

Brian Vancouver -- -- -- --
Jesse Toronto Dallas Vancouver -- --
Steven Dallas Vancouver -- -- --


I need to summarize this info by city so that it may look like Table 1.2.

(Table 1.2)

City SRep1 SRep2 SRep3 SRep4 SRep5

Toronto Jesse
Dallas Jesse Steven
Vancouver Brian Jesse Steven


Can someone PLEASE tell me how I can go about doing this, WITHOUT using a pivottable? Any help would be VERY much appreciated. Thanks in advance.

 

[MDCurry]

If Table 1.1 starts Header row at B1 and Data at B2 etc to look like

Table 1.1
sales rep	City 1	City 2	City 3	City 4	City 5
Brian	Vancouver				
Jesse	Toronto	Dallas	Vancouver		
Steven	Dallas	Vancouver

Then table 1.2 header at say B8 and you enter Toronto at cell B9, Dallas at B10 and Vancouver at B11...

Then formula at C9 is..

=IFERROR(INDEX($B$2:$B$4,MATCH($B9,C$2:C$4,0)),"")

copy and should look like...

Table 1.2					
City	Srep 1	Srep 2	Srep 3	Srep 4	Srep 5
Toronto	Jesse				
Dallas	Steven	Jesse			
Vancouver	Brian	Steven	Jesse

 

[Glow85]

I have entered the most upper-left cell (C25) in table 1.2 as the following:


=IFERROR(INDEX($B$3:$B$22,MATCH($B25,D$3:D$22,0)),"")



This was successfully able to find the first match for that respective city...

HOW do I go about finding the second, third, and fourth match for that same city? When I copy and paste this cell across the row of this table (D25:H25), only the first name is shown in cell C25. The rest of the cells in that row show a blank cell.

Is this because it is looking only through one column every time I paste it over? Keep in mind, the city names may be located ANYWHERE in Table 1.1 between cells C3:G22

The names of all the sales reps are located in Table 1.1 in Cells B3:B22

WHAT AM I DOING WRONG??

 

[MDCurry]

Perhaps I misunderstood.
Implied in my solution is a fundamental assumption about the data in table 1.1. That is, no city will repeat in a given column. Thus in my example Brian at cell B2 listed as Vancouver in C2 and no other rep will have Vancouver in column C.

If this assumption is incorrect and Vancouver (or any city) appears twice in colum C of table 1.1 then the formula will fail...otherwise copying across works fine.