Solved

Lookup problem - multiple criteria

Posted on 2012-03-21
2
327 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now