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
1	Serials	Location Name	Serials	Location 1	Location 2	Location 3	Location 4
2	12345	Munich	44332	Munich	Hamburg
3	44332	Munich	83838	Munich	Berlin
4	83838	Munich	12345	Munich	Berlin	Hamburg
5	92929	Munich
6	12345	Berlin
7	83838	Berlin
8	92929	Berlin
9	12345	Hamburg
10	44332	Hamburg
11	92929	Hamburg

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =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)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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))),"")