LOOK FOR LATEST SELLING PRICE

[Guest]

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..

 

[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

 

[Biff]

OR -

Also entered as an array:

=VLOOKUP(MAX(IF(A2:A5="A1",B2:B5)),B2:C5,2,0)

Biff

 

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

 

[Guest]

Biff,


it doesn't work.. it return #VALUE!.....


Lawrence

 

[Guest]

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 ?

 

[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

 

[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

 

[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