On Dec 18, 7:38*am, Dave Peterson wrote:
If x999 contains theaccountnumber, then you can use this to get the date:
=LOOKUP(2,1/(b1:b100=x999),a1:a100)

Change A1:a100 to d1:d100 for the payment/purchase price.

If you have to look for pmnt in column C at the same time:

=LOOKUP(2,1/((b1:b100=x999)*(c1:c100="Pmnt")),a1:a100)

Both of these are array formulas.

Hit ctrl-shift-enter instead of enter. *If you do it correctly, excel will wrap
curly brackets {} around your formula. *(don't type them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

wx4usawrote:

Can I useSUMPRODUCTto return the date and amount of last payment if
I provide theaccountnumber?

Column A is Date *mm/dd/yyyy
Column B isaccountnumber * * (numeric 6 digits)
Column C is type: PMNT, Purchase
Column D is amount, either a + or - based on Mayment or Purchase

--

Dave Peterson

Dave,

I am still having trouble getting this to work.

Column A is date, B is Account number, C is type (chg or pmnt), D is
amount paid (+or-)

In one cell I have account number to look up. One cell returns the
date of last payment (pmnt) One cell contains the amount of last
payment.

Any help you could provide would be helpful.