Help with Vlookup to report 2nd 3rd 4th etc Matches

[Marsman]

I have been reading post after post and cannot quite figure this out.

I need to be able to do a Vlookup without changing any data as "Helper Columns" etc.

As with the following example "Sheet 1" is where is am trying to have the info populate and "sheet 2" is the Data set.

Obviously when I do "Vlookup" it is finding the information for the 1st record and pasting it into the follwoing record.

The only common column would ever be Column A. the Data in Columns B and C would change consistantly.


Sheet 1

		A B C 1 P20 LPI Value needed 2 P20 PF Value needed 3 P20 SCA Value needed 4 p19 LPI Value needed​ 5 P20 FC Value needed​ 6 P16 SCA Value needed​ 7 P19 DS FEC Value needed​ <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Sheet 2

	A	B	C
1	P20	LPI	DS RX
2	P20	PF	US
3	P20	SCA	US TX
4	p19	LPI	DS RX
5	P20	FC	NR
6	P16	SCA	US RX
7	P19	DS FEC	DS

<tbody>
</tbody>

 

[Marsman]

And you are decided not to affirm whether they are DS RX and US for the first two occurrences of P20...

the issue is the Data in Column B is a small list of different types (around 12 types total) then the data in Column C can be upwards in the area of around 100 different categories.

This is why I was wondering if there was a formula/array to be able to report what is associated with each record "P20" in column A.

 

[azumi]

In Cell B1 (Sheet1):
=IFERROR(INDEX(Sheet2!B$1:B$7,SMALL(IF((Sheet2!$A$1:$A$7=$A1)*(Sheet2!B$1:B$7<>""),ROW(Sheet2!$A$1:$A$7)-ROW(Sheet2!$A$1)+1),COUNTIF($A$1:$A1,$A1))),"-")

Copied Cross and Down

Azumi

 

[azumi]

https://www.dropbox.com/s/44rbxz8ukuo1otg/Multi Results.xlsx

or see the file

 

[Marsman]

In Cell B1 (Sheet1):
=IFERROR(INDEX(Sheet2!B$1:B$7,SMALL(IF((Sheet2!$A$1:$A$7=$A1)*(Sheet2!B$1:B$7<>""),ROW(Sheet2!$A$1:$A$7)-ROW(Sheet2!$A$1)+1),COUNTIF($A$1:$A1,$A1))),"-")

Copied Cross and Down



Azumi

This code works great.

Thanks