return a number from a cell based on the lastest date

S

sarahmarsden

Hello,

I have a list as follows, and I would like to get the lastest Number
Ordered for each Person based on the date.

Date Person Order
10/10/06 Person 1 15
10/10/06 Person 2 25
15/10/06 Person 1 10

ie I would like to see the following as a result

Date Person Order
10/10/06 Person 2 25
15/10/06 Person 1 10

Thanks in advance.
 
B

Bob Phillips

Assuming the data is on sheet1, on another sheet list the dates and names
and add

=INDEX(Sheet1!C1:C100,MATCH((A2=Sheet1!A1:A100)*(B2=Sheet1!B1:B100),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Biff

Since the key is Person X try this:

This data is in the range A2:C4
10/10/06 Person 1 15
10/10/06 Person 2 25
15/10/06 Person 1 10
Click to expand...

A10 = Person 1
A11 = Person 2

Enter this formula in B10 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=MAX(IF(B$2:B$4=A10,A$2:A$4))

Enter this formula in C10 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=INDEX(C$2:C$4,MATCH(1,(B$2:B$4=A10)*(A$2:A$4=B10),0))

Select both B10 and C10 and copy down as needed.

Biff
 
Top