Vlookup from a list with duplicate index values

[feltonam]

Hi all

I am trying to use a Vlookup or similar to populate a table from a list containing multiple values in the index column as below:

ID TEST RESULT
910 HEPA Not Ok
910 HEPB Ok
1234 HEPB Ok
1234 HEPA OK
5678 HEPA Ok
5678 HEPB OK

For each ID I need to return the results of each type of test in a table as follows:
RESULT HEPA HEPB
5678
910
1234

Any help welcome

 

[vgulielmus]

One posibility

If:
A	B	C
ID	TEST	RESULT
910	HEPA	Not Ok
910	HEPB	Ok
1234	HEPB	Ok
1234	HEPA	Ok
5678	HEPA	Ok
5678	HEPB	Ok

Then:
F	G						H
RESULT	HEPA						HEPB
5678	Ok						Ok
910	Not Ok						Ok
1234	Ok						Ok


RESULT	HEPA						HEPB
5678	=INDEX($C:$C;MATCH($F2&G$1;$R$1:$R$7;0))	=INDEX($C:$C;MATCH($F2&H$1;$R$1:$R$7;0))
910	=INDEX($C:$C;MATCH($F3&G$1;$R$1:$R$7;0))	=INDEX($C:$C;MATCH($F3&H$1;$R$1:$R$7;0))
1234	=INDEX($C:$C;MATCH($F4&G$1;$R$1:$R$7;0))	=INDEX($C:$C;MATCH($F4&H$1;$R$1:$R$7;0))


Where :
R		S
ID+TEST		RESULT
910HEPA		Not Ok
910HEPB		Ok
1234HEPB	Ok
1234HEPA	Ok
5678HEPA	Ok
5678HEPB	Ok


ID+TEST		=C1
=A2 & B2	=C2
=A3 & B3	=C3
=A4 & B4	=C4
=A5 & B5	=C5
=A6 & B6	=C6
=A7 & B7	=C7

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[feltonam]

Thanks for your reply, I have solved this by using a pivot and then doing the lookup from that.

Regards

Angela