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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER