vlookup - get value from filtered list

[jagadesh77]

Hi
I have data in a table like below:

Pkg Type	Pkg Name	DEV01	DEV02
CP	cp1	20-11-2017 00:00	21-11-2017 07:00
DP	dp1	19-11-2017 00:00	20-11-2017 00:00
CP	cp2	21-11-2017 00:00	21-11-2017 05:00
DP	dp1	19-11-2017 09:00	20-11-2017 10:00
DB	dp1	19-11-2017 00:00	20-11-2017 00:00
DB	dp1	19-11-2017 05:00	20-11-2017 06:00

<colgroup><col span="2"><col span="2"></colgroup><tbody>
</tbody>

I am expecting values like below, is it possible to get like this? If am fine with re-arranging data to get required output.

	CP	DP	DB
DEV01	Latest pkg of Type 'CP' on DEV01	Latest pkg of Type 'DP' on DEV01	Latest pkg of Type 'DB' on DEV01
DEV02	Latest pkg of Type 'CP' on DEV02	Latest pkg of Type 'DP' on DEV02	Latest pkg of Type 'DB' on DEV02

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thanks
Jag

 

[Eric W]

Welcome to the board.

Try this:

	A	B	C	D	E	F	G	H	I	J	K
1	Pkg Type	Pkg Name	DEV01	DEV02					CP	DP	DB
2	CP	cp1	11/20/2017 0:00	11/21/2017 7:00				DEV01	cp2	dp1	db2
3	DP	dp1	11/19/2017 0:00	11/20/2017 0:00				DEV02	cp1	dp1	db2
4	CP	cp2	11/21/2017 0:00	11/21/2017 5:00
5	DP	dp1	11/19/2017 9:00	11/20/2017 10:00
6	DB	db1	11/19/2017 0:00	11/20/2017 0:00
7	DB	db2	11/19/2017 5:00	11/20/2017 6:00
8
9
10

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

Sheet2​

Array Formulas Cell Formula I2 {=IFERROR(INDEX($B$2:$B$10,MATCH(I$1&"|"&LARGE(IF($A$2:$A$10=I$1,INDEX($C$2:$D$10,0,MATCH($H2,$C$1:$D$1,0))),1),$A$2:$A$10&"|"&INDEX($C$2:$D$10,0,MATCH($H2,$C$1:$D$1,0)),0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>