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?

wx4usa wrote:

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.

--

Dave Peterson