Mutliple Index Match criteria formula needed

[bwmuhich]

Hello Excel Forum.

I have been trying (unsuccessfully) to develop a formula I need for one of my rate calculation worksheets which I don't think should be too terribly challenging as it's not very complex at all, but the formula I have been trying in multiple versions has not accomplished the needed outcome.

I am trying to have a formula that will look up to criteria given a specific if statement. If cell E142 = "Y", then index the range z412:931 and match with two criteria. The first criteria is the number (age) in D142 with the range H412:H931 and the second criteria is to match the value (rating area) in I142 with the range I412:I931.

The formula works as long as the value in I142 is RA1, but if I change it to any of the other rating areas like RA2-RA5, the formula errors.

I need the formula to be able to drag down and to the right. I've attached a sample worksheet for guidance. I need working formula in the green highlighted cells.

Ultimately what I need to do is match someone's smoking status, their age and the rating area they live in and given those 3 data criteria, the formula needs to return the correct rate given the rate tables provided in the excel worksheet.

Thank you in advance for your help.

Be Well.

[mrice]

I thinks that it is something to do with the highlighted segment

=IFERROR(IF($E142="Y",INDEX(Z$412:Z$931,MATCH($D142,$H$412:$H$931,0),MATCH($I142,$I$412:$I$931,0))*(1+Z$302),IF(OR($E142="",$E142="N"),INDEX(Z$412:Z$931,MATCH($D142,$H$412:$H$931,0),MATCH($I142,$I$412:$I$931,0)))),"")

This segment evaluates to 1 for the top cell but when dragged down to the cell below, it evaluates to 105 - there are not that may columns in your range.

Try selecting syntactically sensible segments of the formula in the formula bar and pressing F9 to see what they evaluate to.

[Jacc]

Is it anything like this?

Please Login or Register  to view this content.

[bwmuhich]

unfortunately, no. That won't work.