Excel 2007 : Lookup/Vlookup using two Lookup values

**Rick K** · 09-16-2010, 12:49 PM

The attached spreadsheet is a sales report (first tab) that we create with multiple customers who have multiple business lines. The second tab contains detaled data that shows payment deductions (charges) for each of those customers, by business line.

The third tab is just a pivot table showing the total charges, by customer, by business line.

I want to be able to lookup by customer number, the charge/deduction for each of the business lines. So I guess in essensce, since the cust# is not unique for each line, the lookup command has to look at the customer number AND the business line, then go over to the Pivot Table and see which charge amount matches the customer number and business line, and then return its value.

I've researched the forums, and am becoming more confused as I go.

Any help is appreciated.

Thanks!
Rick

**NBVC** · 09-16-2010, 01:06 PM

Try:

=SUMPRODUCT(--('CHARGE REPORT'!$C$2:$C$210=A2),--('CHARGE REPORT'!$D$2:$D$210=B2),'CHARGE REPORT'!$I$2:$I$210)

or in 2007:

=SUMIFS('CHARGE REPORT'!$I$2:$I$210,'CHARGE REPORT'!$C$2:$C$210,A2,'CHARGE REPORT'!$D$2:$D$210,B2)

you can use the source data instead of the Pivot Report.

**Rick K** · 09-16-2010, 01:19 PM

You have GOT to be kidding! I messed with this formula for an hour! THANKS!!! Now I face a new question: The "charges" that were returned don't equal the total of the data on the charges tab. This means means that there were charge transactions on customers who don't show up on the sales report. Ugh.

**NBVC** · 09-16-2010, 01:29 PM

Can you give example of what you mean?

**Rick K** · 09-16-2010, 02:16 PM

I found the problem and how to fix it. What happens, is that there are customers who do not appear on the Sales Report, who have charges (the Charge Report) against their account, on different business lines. So all I had to do was compare the two reports to find out which customers fit this criteria.

I really appreciate your assistance is helping me to figure this out. We pay commissions based on Net Sales (which what this report really calculates), and now we can proceed and be 100% accurate.

Rick