LOOK FOR LATEST SELLING PRICE

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

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

"Lawrence" <Lawrence@discussions.microsoft.com> wrote in message
news:E7AE60F0-FE6E-4EE2-A273-016C6F19E018@microsoft.com...
> 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..

OR -

Also entered as an array:

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

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:eh93EnXyFHA.664@tk2msftngp13.phx.gbl...
> 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
>
> "Lawrence" <Lawrence@discussions.microsoft.com> wrote in message
> news:E7AE60F0-FE6E-4EE2-A273-016C6F19E018@microsoft.com...
>> 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..
>
>

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.

Lawrence wrote:
> 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,


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


Lawrence


"Biff" wrote:

> 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
>
> "Lawrence" <Lawrence@discussions.microsoft.com> wrote in message
> news:E7AE60F0-FE6E-4EE2-A273-016C6F19E018@microsoft.com...
> > 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..
>
>
>

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" wrote:

> OR -
>
> Also entered as an array:
>
> =VLOOKUP(MAX(IF(A2:A5="A1",B2:B5)),B2:C5,2,0)
>
> Biff
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:eh93EnXyFHA.664@tk2msftngp13.phx.gbl...
> > 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
> >
> > "Lawrence" <Lawrence@discussions.microsoft.com> wrote in message
> > news:E7AE60F0-FE6E-4EE2-A273-016C6F19E018@microsoft.com...
> >> 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..
> >
> >
>
>
>

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

"Lawrence" <Lawrence@discussions.microsoft.com> wrote in message
news:1059316D-E4BC-4DF5-8942-D21059B3F3FB@microsoft.com...
> Biff,
>
>
> it doesn't work.. it return #VALUE!.....
>
>
> Lawrence
>
>
> "Biff" wrote:
>
>> 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
>>
>> "Lawrence" <Lawrence@discussions.microsoft.com> wrote in message
>> news:E7AE60F0-FE6E-4EE2-A273-016C6F19E018@microsoft.com...
>> > 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..
>>
>>
>>

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



Lawrence wrote:

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

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



Roger Govier wrote:

> 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
>
>
>
> Lawrence wrote:
>
>> 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..
>>
>>