Vlookup with multiple results

Resolved · Medium Priority · Version 2007

Wed 13 Nov 2013, 15:24 Delegate Jason said...

Jason has attended:
Excel Advanced course
Excel Intermediate course
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - Formulas & Functions course

Vlookup with multiple results

Hi.

I have 2 tabs in a WB and want to use vlookup or match functionality.

Sales order tab has 2 columns: sales order numbers and the subtotal of purchase accrual by sales order.

COGS tab has line items of actual purchases paid, listed by sales order number.

In Sales order tab, I want to lookup the sales order number and return the sum of ALL line items with the same order number from COGS tab.

many thanks
Jason

Wed 13 Nov 2013, 16:18 Trainer Doug said...

RE: Vlookup with multiple results

Hi Jason

You may be able to achieve the result you want with a SUMIF function rather than a VLOOKUP. In the example below here is the Sumif formula in cell B2 of the Sales Order tab.

=SUMIF(COGS!A:A,A2,COGS!B:B)

Sales Order tab

Order Num Sales accrual
AB123 340
ACB1234 1871
ACB1234 1871
BAW123 1252
BAW999 1446

COGS tab

Line Item Actual Sales
BAW999 977
ACB1234 790
BAW123 129
AB123 93
ACB1234 665
BAW123 771
AB123 247
BAW999 469
ACB1234 416
BAW123 352

Please let me know if that helps. I may have misunderstood the question!

Regards
Doug Dunn
Best STL

Thu 14 Nov 2013, 10:30 Edited on Thu 14 Nov 2013, 10:31 Trainer Doug said...

RE: Vlookup with multiple results

Hi Jason

Glad to hear the SUMIF function achieved the job for you.
Did you know you can add multiple criteria with a SUMIFS function. It may not solve your extra layer of complexity but it's good to know for future.

Suppose the data in your COGS tab looks like this

Line Item Actual Sales Month
BAW999 977 1
ACB1234 790 1
BAW123 129 1
AB123 93 1
ACB1234 665 2
BAW123 771 2
AB123 247 2
BAW999 469 2
ACB1234 416 2
BAW123 352 3

You can use this formula to find a total for a specific Order Num and a specific Month number such as 2.

=SUMIFS(COGS!B:B,A2,COGS!A:A,"2",COGS!C:C)

Notice the sum range comes first this time.

Does this relate to your question an some way?
Doug

Wed 13 Nov 2013, 16:56 Delegate Jason said...

RE: Vlookup with multiple results

Brilliant. It works.
now to add another layer of complexity.

in the second tab of actual line items, each line item may have a different paid date (month).

i want to be able to itemise the line data by month by probably using a filter, to then home in on payments made in any one month.

e.g
Accrual in month 1 for £100
1st actual pmt in mth 1 for £(50)
2nd actual pmt in mth 2 for £(25)
3rd actual pmt in mth 4 for £(30)
Final true-up difference on actual is £(5)

in month 2, i want to be able to show the cummulative true up figure for month 2.
ie. Accrual less pmt1 and pmt2 = £25,

and what was booked in month 4 = £(5).

can this be done?

regards

Jason

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Remove unused toolbar buttons

Are there buttons on your Excel toolbars that you never use? Remove them from the toolbar by doing the following:

1. Go to Tools - Customise - Commands.
2. Select the toolbar button you wish to remove, then use your mouse to drag and drop the button into the Excel window. When you release your finger from the mouse, the button will disappear.

View all Excel hints and tips