# Index Match with Horizontal and Vertical Matches

1. ## Index Match with Horizontal and Vertical Matches

I have a budget related spreadsheet that calculates rates based on various criteria.

1. Either original budget or forecast budget
2. Either rehearsal rate or concert rate
3. Either regular pay rate or doubling pay rate
4. Either of two contract term time periods

I have the spreadsheet working except for the fact that I can not figure out how to have the entire formula determine if its one contract term vs. another. Currently, the example spreadsheet formula (built in the project tab) is only looking at the one row of contract term rates. But if you look in the project spreadsheet, you'll see that in column A, the row is based on one of two contract term time periods. The rate grid is also based on two contract term time periods but my formula isn't acknowledging both contract term periods at this point.

I started this whole formula with a vlookup on the contract term period and the rehearsal rate or concert rate fields. That all worked perfect. Then I realized that that method would not capture the fact that there are two types of pay rates - regular or doubling.

Is the rate grid even set up the right way? This can be redone if this would help resolve my issue.

I realize at this point that I have two horizontal matches and I'm trying to add in a vertical match....is that possible?

Thanks.  Register To Reply

2. ## Re: Index Match with Horizontal and Vertical Matches

Though I do not understanding clearly, but for the second term, your reference data :
'Rate Grid'!\$C\$8:\$N\$8
may be:
'Rate Grid'!\$C\$9:\$N\$9
and the result wil be corrected.  Register To Reply

3. ## Re: Index Match with Horizontal and Vertical Matches

I can not use your suggested formula above since it needs to run off of row 8 OR 9. The problem is that I need to add column A from the project tab into the formula. Right now, the formula is only set to look at Row 8 (as it was built) but what I need it to do it look at row 8 OR row 9 of the rate grid based on what 's in column A of the project tab. Does that help state my question better? Thank you!  Register To Reply

4. ## Re: Index Match with Horizontal and Vertical Matches

Index Match2.xlsxAlright, I figured out the answer to my own question. The formula I came up with is as such:

=IF(\$B4="9/15-12/15",INDEX('Rate Grid'!\$C\$8:\$N\$8,MATCH(1,(Project!\$D\$1='Rate Grid'!\$C\$4:\$N\$4)*(Project!H\$2='Rate Grid'!\$C\$5:\$N\$5),0))*Project!I4*Project!\$D4+INDEX('Rate Grid'!\$C\$8:\$N\$8,MATCH(1,(Project!\$E\$1='Rate Grid'!\$C\$4:\$N\$4)*(Project!H\$2='Rate Grid'!\$C\$5:\$N\$5),0))*Project!I4*Project!\$E4,IF(\$B4="12/16-8/31",INDEX('Rate Grid'!\$C\$9:\$N\$9,MATCH(1,(Project!\$D\$1='Rate Grid'!\$C\$4:\$N\$4)*(Project!H\$2='Rate Grid'!\$C\$5:\$N\$5),0))*Project!I4*Project!\$D4+INDEX('Rate Grid'!\$C\$9:\$N\$9,MATCH(1,(Project!\$E\$1='Rate Grid'!\$C\$4:\$N\$4)*(Project!H\$2='Rate Grid'!\$C\$5:\$N\$5),0))*Project!I4*Project!\$E4,0))

Now I'd like to know if there is an easier way to do this.

I was hoping to avoid writing the formula so that 1) it was locking the formula into being based on a defined if statement....what if the dates need to change. Then I'll have to find / replace very worksheet in the entire workbook for this formula. 2) What if new contract terms - date range needed to be created. It requires me to extend the formula, vs. just updating a named range.

I'm still hoping for someone to point out an easier way to set up the Rate Grid or the formula itself to look up vertically and horizontally off of numerous criteria.

Thanks.
Katie  Register To Reply