On Dec 19, 11:24*am, Dave Peterson wrote:
What formula did you use?

If you put the values to match in other cells, what were the values and what
were the addresses of the cells?

If you're matching on numbers, did you enter a numeric value? *
If you're matching on digits that are text, did you enter the value the same way
(as text)?

Did you remember to hit ctrl-shift-enter?



wx4usawrote:

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 isAccountnumber, C is type (chg or pmnt), D is
amount paid (+or-)

In one cell I haveaccountnumber 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.

--

Dave Peterson

I did get it to return the amount that corresponds to a payment on an
account.

But now how do I add the last payment criteria which would be the max
date + account column + pmnt column and return the date and amount of
that payment.

I may have 20 or 30 payments on each of the accounts and I need the
date and amount of the last one. Does that make sense? Thanks Dave