Lookup problem - multiple criteria

I need to plug data into a formula, but I need a lookup of some kind that will accept multiple criteria to pinpoint the cell with that data.  There is an excel file attached to this question, and it contains two worksheets.  The first worksheet is where I will be doing the formula work and the second worksheet contains the reference data.

 The first worksheet "ProducerComms": columns B, D and E need to be used as lookup criteria to return the value contained within a specific cell on the next worksheet, "PremiumRates".  

I want to match the age value in the column ANBTo against the column labeled "ANB" on the second worksheet to get the correct row.  Then I want to find the right column on PremiumRates by using ProductCode and Gender values in columns B,D from ProducerComms.  There are two columns of data for each product to differentiate the pricing betwen male and female.  By knowing ANB, ProductCode and Gender I should be able to return only one value with this lookup.

The value returned (calling it "ReferenceValue") will be used within the formula found in column J, "AnnualizedPremium" : $B$1/1000*ReferenceValue.  So, it isn't a tough formula but it is a tough lookup (at least for me!).
CommissionConversionTestCases---.xlsx
kbdaemonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
You say you want to use columns B, D and E as lookup criteria but also that you want to use the "ANBTo" value....which is in column F, I'm assuming F is right in which case you can get the reference value with this formula

=SUMPRODUCT((PremiumRates!B$2:Q$2=B3)*(PremiumRates!$B$3:$Q$3=D3)*(PremiumRates!A$4:A$79=F3),PremiumRates!B$4:Q$79)

change F3 to E3 if my assumption is wrong

That would make the whole formula for J3

=B$1/1000*SUMPRODUCT((PremiumRates!B$2:Q$2=B3)*(PremiumRates!$B$3:$Q$3=D3)*(PremiumRates!A$4:A$79=F3),PremiumRates!B$4:Q$79)

copy down column

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbdaemonAuthor Commented:
AMAZING - now I have to go read up on how that works.  Thanks very much Barry!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.