Mutliple Index Match criteria formula needed

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.

unfortunately, no. That won't work.