a typical formula

[Deepak]

Hi

I've the following data in a file:

A B C
Phone Recharge Date Amount
123456 4/14 2300
234567 6/10 5800
123456 5/14 5800
234567 7/10 3400
123456 6/14 3000


Earlier, i got a solution to find out the MAXIMUM of the "Active Date" e.g.

for Phone No. 234567 Last active date is 7/10 using formula

{MAX(IF(recharge_Query!A$1:B$2000=A2,recharge_Query!E$1:E$2000))}

given by YOU genius out there...

Now i want to get the LAST RECHARGED AMOUNT as per the following format on a
new sheet:
eg.
Phone no. Last Recharge Dt. Last Recharge Amount
123456 6/14 3000
234567 7/10 3400

Please suggest a formula.

Reg
Deepak

 

[Frank Kabel]

Hi
try the following array formula (entered with
CTRL+SHIFT+ENTER)

=INDEX(C1:C100,MATCH(1,(A1:A100="123456")*(B1:B100=MAX(IF
(A1:A100="123456",B1:B100))),0))