LOOK FOR LATEST SELLING PRICE

L

Lawrence

currently, my boss asked me to look for latest price in a large volumn of
data in excel.

the data is like this...

Product transaction date selling price
A1 5/6/2005 $10
A2 6/6/2005 $11
A1 7/6/2005 $12
A1 8/6/2005 $10.5

Is there any formulas allow to get the A1 latest selling price?

thanks a lot..
 
B

Biff

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(C2:C5,MATCH(1,(A2:A5="A1")*(B2:B5=MAX(IF(A2:A5="A1",B2:B5))),0))

Biff
 
A

Aladin Akyurek

With E2 housing a product of interest:

1. If the transaction dates per product is an ascending series...

=LOOKUP(2,1/($A$2:$A$5=E2),$C$2:$C$5)

2.

=INDEX($C$2:$C$5,MATCH(MAX(IF($A$2:$A$5=E2,$B$2:$B$5)),$B$2:$B$5,0))

which needs to be confirmed with control+shift+enter.
 
L

Lawrence

Bilf,

Both also work, i forgotten to press key in combo.

can i know what does it function by "key combo of CTRL,SHIFT,ENTER"

beside that, if the product go by thousand of items.. is it i have to press
the combo everytime ?
 
B

Biff

Did you enter the formula as an array?

Type the formula, then instead of hitting the enter key hold down the CTRL
and SHIFT keys then hit ENTER. When done properly Excel will place squiggly
braces { } around the formula. You cannot just type those braces in, you
MUST use the key combination.

Biff
 
R

Roger Govier

Hi Lawrence

Yet another variation for a solution. The non array formula
=SUMPRODUCT(--(A2:A5="A1"),--(B2:B5=MAX(B2:B5)),C2:C5)

Regards

Roger Govier
 
R

Roger Govier

Hi Lawrence

Forget that. It's nonsense.
It works for your sample set of data, but won't work of course if the
latest date isn't on a line with A1.

Regards

Roger Govier
 
Top