# Mutliple Index Match criteria formula needed

1. ## 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.

2. ## Re: Mutliple Index Match criteria formula needed

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.

3. ## Re: Mutliple Index Match criteria formula needed

Is it anything like this?
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Mutliple Index Match criteria formula needed

unfortunately, no. That won't work.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1