Vlookup Challenging VLookup

[JMC57]

Looking at the headers below I want to build a look up formula that will result in the latest (most recent date in list) transaction date (A) for a specific transaction type (B) for the MedID (D) for Station (E) and return the associated information in the desired results format listed below the original format.<o></o>

A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​
TransactionDate<o></o>	Transaction<o></o>	MedDescription<o></o>	MedID<o></o>	Station<o></o>

<TBODY>
</TBODY>

<o> </o>
Desired results format<o></o>

A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​
MedID<o></o>	Station<o></o>	TransDate Withdrawn<o></o>	TransDate Loaded<o></o>	Trans Date Unloaded<o></o>

<TBODY>
</TBODY>

<o> </o>
There are 3 transaction types. Loaded, Unloaded and Withdrawn. What I really need to know looking at over 60,000 lines of data: The date of most recent withrawn for a MedID, the most recent Loaded transaction if there is one and finally the most recent unloaded date is there is one line.<o></o>
Any help, suggestions will be appreciated. Thanks<o></o>

 

[Andrew Poulsom]

Can we see some sample data please?

 

[JMC57]

[TransactionDate]</SPAN>	[Transaction] </SPAN>	[MedDescription]</SPAN>	[MedID]</SPAN>	[Station]</SPAN>
06/08/2013</SPAN>	LOADED</SPAN>	CARBAMAZEPINE200.000MGUDTAB </SPAN>	TEGROT2002</SPAN>	5N1M</SPAN>
05/16/2013</SPAN>	LOADED</SPAN>	HYDROCODONE 5MG/APAP 3251.000TABTAB</SPAN>	HYDR-2899</SPAN>	5N1M</SPAN>
05/28/2013</SPAN>	LOADED</SPAN>	HYDROCODONE 10MG/APAP 3251.000TABTAB</SPAN>	HYDR-2239</SPAN>	5N1M</SPAN>
06/08/2013</SPAN>	UNLOADED</SPAN>	CARBAMAZEPINE200.000MGUDTAB</SPAN>	TEGROT2002</SPAN>	5N1M</SPAN>
05/30/2013</SPAN>	UNLOADED</SPAN>	HYDROCOD 5/APAP 5001.000UDTABTABLET</SPAN>	HYDROT5102</SPAN>	5N1M</SPAN>
06/03/2013</SPAN>	UNLOADED</SPAN>	HALOPERIDOL2.000MGTAB</SPAN>	HALDOT2</SPAN>	5N1M</SPAN>
06/03/2013</SPAN>	UNLOADED</SPAN>	HALOPERIDOL0.500MGUDTAB</SPAN>	HALOOT.513</SPAN>	5N1M</SPAN>
05/13/2013</SPAN>	WITHDRAWN</SPAN>	BUSPIRONE HCL5.000MGTABLET</SPAN>	BUSPOT5101</SPAN>	5N1M</SPAN>
05/13/2013</SPAN>	WITHDRAWN</SPAN>	BUSPIRONE HCL5.000MGTABLET</SPAN>	BUSPOT5101</SPAN>	5N1M</SPAN>
06/09/2013</SPAN>	WITHDRAWN</SPAN>	CARBAMAZEPINE100.000MGTAB</SPAN>	TEGROT1001</SPAN>	5N1M</SPAN>
06/09/2013</SPAN>	WITHDRAWN</SPAN>	CARBAMAZEPINE100.000MGTAB</SPAN>	TEGROT1001</SPAN>	5N1M</SPAN>
06/08/2013</SPAN>	WITHDRAWN</SPAN>	CARBAMAZEPINE100.000MGTAB</SPAN>	TEGROT1001</SPAN>	5N1M</SPAN>
06/08/2013</SPAN>	WITHDRAWN</SPAN>	CARBAMAZEPINE100.000MGTAB</SPAN>	TEGROT1001</SPAN>	5N1M</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>

 

[Andrew Poulsom]

Example with your data in A1:E14 (the formula cells are formatted as mm/dd/yyyy;; to suppress the display of zero):

Excel 2010
	G	H	I	J	K
1	MedID	Station	Withdrawn	Loaded	Unloaded
2	TEGROT2002	5N1M		06/08/2013	06/08/2013
3	TEGROT1001	5N1M	06/09/2013
Sheet3

Cell Formulas
Range	Formula
I2	=MAX(INDEX(($D$2:$D$14=$G2)*($E$2:$E$14=$H2)*($B$2:$B$14=I$1)*$A$2:$A$14,))

 

[JMC57]

Thanks! You are very helpful.

Example with your data in A1:E14 (the formula cells are formatted as mm/dd/yyyy;; to suppress the display of zero):

Excel 2010

	G	H	I	J	K
1	MedID	Station	Withdrawn	Loaded	Unloaded
2	TEGROT2002	5N1M		06/08/2013	06/08/2013
3	TEGROT1001	5N1M	06/09/2013

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet3​

Worksheet Formulas Cell Formula I2 =MAX(INDEX(($D$2:$D$14=$G2)*($E$2:$E$14=$H2)*($B$2:$B$14=I$1)*$A$2:$A$14,)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[JMC57]

How do I format the formula to show what you have in G though K. I could not replicate your results. Thanks

 

[Andrew Poulsom]

G and H are manual inputs. The formula in I2 copied down and across is:

=MAX(INDEX(($D$2:$D$14=$G2)*($E$2:$E$14=$H2)*($B$2:$B$14=I$1)*$A$2:$A$14,))

Those cells are formatted as:

mm/dd/yyyy;;