?
Solved

Lookup problem - multiple criteria

Posted on 2012-03-21
2
Medium Priority
?
344 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:kbdaemon
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 37749886
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
 

Author Closing Comment

by:kbdaemon
ID: 37749914
AMAZING - now I have to go read up on how that works.  Thanks very much Barry!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question