Creating a lookup that references multiple columns

I am working on an internal audit of our accounts receivable activity where I would like to match our billings with the customers payments, both of which are referenced by an order number. The twist is that the order number assigned to the billings and payments is listed multiple times in each schedule, each time referring to a different item on the billing and payment. I would like to have a lookup that first finds the order number, then finds the item number and returns the payment data for the item, so I can determine the differences between the amounts billed and amounts paid. Here are the details:

Billing information-
Order No. Item No. Description Invoice No. Invoice Date Lines Billing
1003580 500000230 Argina XL 40 5007514 10/12/2010 1 70,129.46
1004099 500000046 Melina S30 5007550 10/12/2010 3 39,630.00
1004099 500000095 Alexia 50 5007550 10/12/2010 3 152,650.19
1004099 500000439 Argina T30 5007550 10/12/2010 3 39,550.74
1004101 500000124 Gadinia 40 5007551 10/12/2010 2 20,260.56
1004101 6510435203 Omala 150 5007551 10/12/2010 2 250.50

Payment information-
(This is what I would like to match and return to the first worksheet)
Order No. Item No. Description Quantity UOM Item Net Price Payment
1003580 500000230 Shell Argina XL 40 BULK 7,027.00 GAL 18.09 127,118.43
1004099 500000046 Shell Melina S 30 BULK 3,963.00 GAL 10.29 40,779.27
1004099 500000095 Shell Alexia 50 BULK 17,171.00 GAL 9.18 157,629.78
1004099 500000439 Shell Argina T 30 BULK 3,963.00 GAL 10.27 40,700.01
1004101 500000124 Shell Gadinia 40 BULK 2,378.00 GAL 8.81 20,950.18
1004101 6510400055 Shell Omala Oil 150 1*400lb 5 EA 512.55 2,562.77

I have about 300 transactions to analyze, so it seems that this is a perfect application for Excel. I tried using VLOOKUP with MATCH, but didn't do so well.

I look forward to your suggestions.

Got it


Hi there!  There are several approaches to this, but I'll describe the two that I would use, and hopefully, you should be good.  The first approach uses the SUMIFS function, and the second approach uses a concatenated VLOOKUP.

The SUMIFS approach to me is the way to go, it is the cleanest, and easiest.  You'll need Excel 2007 or 2010 to do this.  If you are on an older version of Excel, then refer to the second approach described below.  The SUMIFS function is technically referred to as a conditional summing function, but you can use it in your example.  In essence, you would tell the SUMIFS function to return the sum of the payment transactions where Order#=Order# and where Item#=Item#.  You see, two criteria (columns) must be met in order for the row to be included in the results.  This is unlike the VLOOKUP function, which can only handle a single condition (column).

If you don't have Excel 2007/2010, then you can use the CONCATENATE function to join the order number and item number columns into a single column for both tables.  Then, you can use VLOOKUP to match on the single column.

Good luck, and if you get stuck, feel free to email me the workbook, and I'll write the formula and send it back.


Thanks again, Jeff. You

Thanks again, Jeff. You saved me.


I like your style

The sumifs is a powerful formula, but can be tricky to use. It is great formula for summing data with like criteria. Is there a way to use a reference cell that has a drop down box of available data?

Drop down


Welcome! Sure, you can use a reference cell that has a drop down box of available data to choose from.  There are a couple of different ways to set up a drop down box in a cell.  The way I prefer is the Data Validation feature.  Select the cell or range of cells for the drop down box, and then open the Data Validation dialog box and then select Allow "List."  You then specify the source of the list (the list of choices).  Once complete, when the user selects the cell to which you've applied Data Validation, Excel will display a combo box and the user can select a value from the list of choices.  If this cell is then used as an argument in a SUMIFS function, you can dynamically control the function results...that is a cool idea.

Good luck, and hit me back anytime.


Creating a lookup that references multiple columns