look up values horizontally

[Urbanx]

Hi All, have a question.


In C column I have my product serials. I want to look for them in column A and if they have a match I want the City names in D3, E3, F3 etc. So I want them horizontally.

Tried to to it with index and match, managed to do one line but then were not able to pull it down because of the $ signs were "fixing" C3 cell.

I just coloured some cells to make it easy to see what I want.

Vlookup obviously brings up only the very first match I need all of them.

Could you help me please?

 

[Toadstool]

Edited as I forgot the restriction with pasting chevrons in HTML.

If I interpret your request correctly then this should work. You need to copy right the maximum number of cities per serial.

The COUNTIF returns null when you've exhausted matching cities for a serial.

The INDEX returns the city from the row which the...
AGGREGATE has returned as it uses the SMALL function returning the 1st, 2nd, 3rd etc. across the COLUMNS frrom D where there's a matching serial.

	A	B	C	D	E	F	G
Location Name	Location 1	Location 2	Location 3	Location 4
Munich	Munich	Hamburg
Munich	Munich	Berlin
Munich	Munich	Berlin	Hamburg
Munich
Berlin
Berlin
Berlin
Hamburg
Hamburg
Hamburg

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Serials[/TD]

[TD="align: center"]Serials[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]12345[/TD]

[TD="align: center"]44332[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]44332[/TD]

[TD="align: center"]83838[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]83838[/TD]

[TD="align: center"]12345[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]92929[/TD]

[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]12345[/TD]

[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]83838[/TD]

[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]92929[/TD]

[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]12345[/TD]

[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]44332[/TD]

[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]92929[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2
[/TH]
[TD="align: left"]=IF(COUNTIF($A$2:$A$11,$C2) < COLUMNS($D$1:D$1),"",INDEX($B$2:$B$11,AGGREGATE(15,6,ROW($A$2:$A$11)-ROW($A$1)/($A$2:$A$11=$C2),COLUMNS($D$1:D$1))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Similar to Toadstool.

=IFERROR(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-ROW($B$2)+1)/($A$2:$A$11=$C2), COLUMNS($A$1:A$1))),"")