Solved

Lookup problem - multiple criteria

Posted on 2012-03-21
2
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 the scrolling table in Microsoft Excel using the INDEX function.

724 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