Excel: Search column for specified value, return value in same row different column

Question

here's my problem:

I have a system-generated report that I can update easily. However, the report is very long and complex and I'm trying to use the complex report to consolidate it to a simpler summary report.

My system outputs a list of items sold with invoice number references and paid/unpaid status. It does not give me a clear list of invoices. What I need is to take the long list and make a short list of invoices with paid/unpaid status.

Example: Sheet "Detail"

Sheet "Summary"

So what's needed is for a formula to look up invoices in the Detail sheet that match values in Column A on the summary sheet and return the status into column B. I will also need the same or similar formula to return the total amount of the invoice into column C This should be such a simple fix yet here I am going crazy...

Solution

Have you tried VLOOKUP, e.g. in B2 on summary sheet

=VLOOKUP(A2,Detail!A:E,4,0)

That will give you the fourth column data from the relevant row (i.e. the Status). for the amount you want column 5 so change that to this formula in C2

=VLOOKUP(A2,Detail!A:E,5,0)

copy both formulas as far cown the columns as required....

From your comment it seems that you want to sum possibly several amounts for the second formula....so use SUMIF to do that, i.e.

=SUMIF(Detail!A:A,A2,Detail!E:E)

Comments

[zer09]

for the second part, however, it is not working. I'm getting a return of the first value it finds as opposed to the entire invoice. So if an invoice is being referenced 3 times, instead of the total for the invoice I'm getting the first occurence amount and I need the total. Thanks again.

[barry houdini]

OK, I though you would just have a single match - for multiple matches use SUMIF - I'll update my answer......