Solved

Lookup problem - multiple criteria

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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