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"

Detail

Sheet "Summary"

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...

barry houdini · Accepted Answer · 2013-04-24 19:20:42Z

2

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)

edited Apr 24, 2013 at 19:20

answered Apr 24, 2013 at 17:36

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zer09 Over a year ago

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 Over a year ago

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

zer09 · Accepted Answer · 2013-04-24 19:31:23Z

0

The second part of the question requires a SUMIF or SUMIFS formula. For the simplified example I listed here, the formula is:

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

My actual problem required a SUMIFS formula since I had repeating items on the same invoice (think of it as 5 line items of "tire" instead of 1) and I needed to add a by-invoice-by-item summary.

answered Apr 24, 2013 at 19:31

zer09

311 gold badge1 silver badge3 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related