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>
[TABLE="class: MsoNormalTable, width: 510"]
<TBODY>[TR]
[TD="width: 136, bgcolor: transparent"]

A<o></o>​

[/TD]
[TD="width: 136, bgcolor: transparent"]

B<o></o>​

[/TD]
[TD="width: 136, bgcolor: transparent"]

C<o></o>​

[/TD]
[TD="width: 136, bgcolor: transparent"]

D<o></o>​

[/TD]
[TD="width: 136, bgcolor: transparent"]

E<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 136"]TransactionDate<o></o>
[/TD]
[TD="width: 136"]Transaction<o></o>
[/TD]
[TD="width: 136"]MedDescription<o></o>
[/TD]
[TD="width: 136"]MedID<o></o>
[/TD]
[TD="width: 136"]Station<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<o> </o>
Desired results format<o></o>
[TABLE="class: MsoNormalTable, width: 280"]
<TBODY>[TR]
[TD="width: 61, bgcolor: transparent"]

A<o></o>​

[/TD]
[TD="width: 61, bgcolor: transparent"]

B<o></o>​

[/TD]
[TD="width: 99, bgcolor: transparent"]

C<o></o>​

[/TD]
[TD="width: 73, bgcolor: transparent"]

D<o></o>​

[/TD]
[TD="width: 79, bgcolor: transparent"]

E<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 61"]MedID<o></o>
[/TD]
[TD="width: 61"] Station<o></o>
[/TD]
[TD="width: 99"]TransDate Withdrawn<o></o>
[/TD]
[TD="width: 73"]TransDate Loaded<o></o>
[/TD]
[TD="width: 79"]Trans Date Unloaded<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<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]

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

 

[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
MedID	Station	Withdrawn	Loaded	Unloaded
TEGROT2002	5N1M
TEGROT1001	5N1M

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]06/08/2013[/TD]
[TD="align: right"]06/08/2013[/TD]

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

[TD="align: right"]06/09/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet3​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]I2[/TH]
[TD="align: left"]=MAX(INDEX(($D$2:$D$14=$G2)*($E$2:$E$14=$H2)*($B$2:$B$14=I$1)*$A$2:$A$14,))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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;;