vlookup - get value from filtered list

[jagadesh77]

Hi
I have data in a table like below:
[TABLE="width: 346"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Pkg Type[/TD]
[TD]Pkg Name[/TD]
[TD]DEV01[/TD]
[TD]DEV02[/TD]
[/TR]
[TR]
[TD]CP[/TD]
[TD]cp1[/TD]
[TD="align: right"]20-11-2017 00:00[/TD]
[TD="align: right"]21-11-2017 07:00[/TD]
[/TR]
[TR]
[TD]DP[/TD]
[TD]dp1[/TD]
[TD="align: right"]19-11-2017 00:00[/TD]
[TD="align: right"]20-11-2017 00:00[/TD]
[/TR]
[TR]
[TD]CP[/TD]
[TD]cp2[/TD]
[TD="align: right"]21-11-2017 00:00[/TD]
[TD="align: right"]21-11-2017 05:00[/TD]
[/TR]
[TR]
[TD]DP[/TD]
[TD]dp1[/TD]
[TD="align: right"]19-11-2017 09:00[/TD]
[TD="align: right"]20-11-2017 10:00[/TD]
[/TR]
[TR]
[TD]DB[/TD]
[TD]dp1[/TD]
[TD="align: right"]19-11-2017 00:00[/TD]
[TD="align: right"]20-11-2017 00:00[/TD]
[/TR]
[TR]
[TD]DB[/TD]
[TD]dp1[/TD]
[TD="align: right"]19-11-2017 05:00[/TD]
[TD="align: right"]20-11-2017 06:00
[/TD]
[/TR]
</tbody>[/TABLE]


I am expecting values like below, is it possible to get like this? If am fine with re-arranging data to get required output.
[TABLE="width: 318"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]CP[/TD]
[TD]DP[/TD]
[TD]DB[/TD]
[/TR]
[TR]
[TD]DEV01[/TD]
[TD]Latest pkg of Type 'CP' on DEV01[/TD]
[TD]Latest pkg of Type 'DP' on DEV01[/TD]
[TD]Latest pkg of Type 'DB' on DEV01[/TD]
[/TR]
[TR]
[TD]DEV02[/TD]
[TD]Latest pkg of Type 'CP' on DEV02[/TD]
[TD]Latest pkg of Type 'DP' on DEV02[/TD]
[TD]Latest pkg of Type 'DB' on DEV02[/TD]
[/TR]
</tbody>[/TABLE]
Thanks
Jag

 

[Eric W]

Welcome to the board.

Try this:

	A	B	C	D	E	F	G	H	I	J	K
Pkg Type	Pkg Name	DEV01	DEV02	CP	DP	DB
CP	cp1	DEV01	cp2	dp1	db2
DP	dp1	DEV02	cp1	dp1	db2
CP	cp2
DP	dp1
DB	db1
DB	db2

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

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

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

[TD="align: right"]11/20/2017 0:00[/TD]
[TD="align: right"]11/21/2017 7:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]11/19/2017 0:00[/TD]
[TD="align: right"]11/20/2017 0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]11/21/2017 0:00[/TD]
[TD="align: right"]11/21/2017 5:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]11/19/2017 9:00[/TD]
[TD="align: right"]11/20/2017 10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]11/19/2017 0:00[/TD]
[TD="align: right"]11/20/2017 0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]11/19/2017 5:00[/TD]
[TD="align: right"]11/20/2017 6:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]