Matching Information

[Unexcellent]

I have a spreadsheet that lists a large number of invoices, dates and values
and payments - How do I look at an invoice go down the list of payments and
then extract in to a cell the value that's been paid - That way I can see if
anything has been paid against the invoice

 

[Mike H]

You don't give many clues to your data layout but ths may be something you
can build on

=SUMPRODUCT((A1:A25=123456)*(B1:B25))
Where column A are you invoice numbers and column B are payments against
each invoice. Change the ranges to suit

Mike

 

[Unexcellent]

Hi Mike

Guess I didn't explain my self very well

Each month we send out apprx 500 invoices to 1 customer

We record the inv no, date & value in columns & add to it each month

We then get a payment in each month listing inv no paid and value & put that
in columns & add to it each month

What I want to do is for each inv no that we've created is look down the
entire column that contains the payment inv no and then create a formula that
puts the payment value in the same row as our inv no

That way I know what has been paid against each of our invoices

Hope this explains better

 

[Tim879]

You can use a vlookup to do the matching you're referring to below...

assuming your workbook is set up as follows:

Col A: Invoice #
Col B: Invoice Amt (amount)

Col C: Vlookup to match the invoice amt w/ the pmt amt (see below)

Col J: Invoice # from pmt
Col K: Pmt amt

For the lookup, use formula like this:
=IF(ISERROR(VLOOKUP(A2,$J:$K,2,FALSE)),"Invoice not
paid",VLOOKUP(A2,$J:$K,2,FALSE))

A2 refers to the invoice #
Col J contains the invoice # from the pmt / Col k contains the amount
of the pmt


Here's my sample data and the results:

Col A Col B Col C (contains
formula) Col J Col K
Invoice # Invoice Amount Amount Paid Invoice # Payment
100001 1,000.00 500
100001 500
100251 937.50 10000 100251
10000
100501 878.91 Invoice not paid

 

[Max]

Assume the 3 source cols in A to C, data in row 2 down (col A = Inv)
Assume payment listing (Inv, Amt) is pasted into F2:G2 down

Place this in D2:
=IF(ISNA(MATCH(A2,F:F,0)),"",INDEX(G:G,MATCH(A2,F:F,0)))
Copy D2 down as far as required

 

[Unexcellent]

Thanks for the replies - What ahappens if the info I'm looking up is
alphanumerical?

 

[Max]

What happens if the info I'm looking up is alphanumerical?

The index/match suggestion should work fine irrespective,
unless there is a data consistency issue

---