Function to find row with most current date
 

I'm wondering if there's a way I could make a function where it would
use values from a certain row by means of the most current date within
that row.

Pretend I have

A1="5/25/2005" B1="NaOH" C1="$120.00"
A2="6/01/2005" B2="NaOH" C2="$145.00"
A3="6/22/2005" B3="HCl" C3="$100.00"

so now I want E1 to show me NaOH's most recent price based on column
A's date and (of course) column B's chemical (for reference to the
correct price).

Is this duable?


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=381354

On Wed, 22 Jun 2005 13:44:54 -0500, malik641
wrote:


I'm wondering if there's a way I could make a function where it would
use values from a certain row by means of the most current date within
that row.

Pretend I have

A1="5/25/2005" B1="NaOH" C1="$120.00"
A2="6/01/2005" B2="NaOH" C2="$145.00"
A3="6/22/2005" B3="HCl" C3="$100.00"

so now I want E1 to show me NaOH's most recent price based on column
A's date and (of course) column B's chemical (for reference to the
correct price).

Is this duable?

Try this:

Assumptions:

Date = named range A1:An
Chemical = named range B1:Bn
Price = named range C1:Cn

G2: Name of chemical for which you need price.

This *array* formula should work:

=INDEX(A1:C10,MATCH(MAX(Date*(Chemical=G2)),Date*( Chemical=G2),0),3)

To enter an *array* formula, after typing or pasting it into the formula bar,
hold down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.

Note that if you decide to have a label row in row 1, you will need to adjust
your references or you may get a VALUE error.


--ron

Awesome!! worked out just fine after a little fine tuning.

this is what I ended up with:

{=INDEX('Data Entry'!$G$2:$G$5000,MATCH(MAX('Data
Entry'!$A$2:$A$5000*('Data Entry'!$B$2:$B$5000=$A5)),'Data
Entry'!$A$2:$A$5000*('Data Entry'!$B$2:$B$5000=$A5),0))}

Thanks for the help ron, it was extremely useful!


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=381354

On Thu, 23 Jun 2005 09:28:56 -0500, malik641
wrote:

Awesome!! worked out just fine after a little fine tuning.

I'm glad you got it working. Thank you for the feedback.


--ron