Index match with multiple criteria

[Kristina1976]

So I think what I need is a index match formula but not 100% sure.

Basically what I need is an insurance calculator which takes into account age, *** and
smoking status determine the premium based on amount of coverage requested.

I've included an attachment with the template.

On worksheet #1 includes variables (age, ***, smoking status, insurance coverage). Colum B I have used validations so only the user can only select the age range, if they are male/female etc.

So what I need is based on the above criteria search in worksheet 2 to come up with the correct premium.


Worksheet # 2 has all of the rates applicable to the age band, *** and smoking status. Rates are per $1,000 of coverage

[JBeaucaire]

1) On the Opt Life Rates tab, highlight the range B5:G14 and give it a name of Rates in the Name box
2) Highlight the range B3:G3 and name it Gender

3) On the Calculator tab, put this formula in F17:

=INDEX(Rates, MATCH(F6, Age, 0), MATCH(F8, Gender, 0) +(F10="non-smoker")) * F14

...and format as currency.

[JBeaucaire]

Formulas updated with another named range for gender.

[Kristina1976]

Thanks for your help, this works great!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.