Function to find row with most current date

[malik641]

I'm wondering if there's a way I could make a function where it woul
use values from a certain row by means of the most current date withi
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 colum
A's date and (of course) column B's chemical (for reference to th
correct price).

Is this duable

 

[Ron Rosenfeld]

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

 

[malik641]

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!

 

[Ron Rosenfeld]

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

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


--ron