Index formula versus If And

maineL

Ok so here is what I have now:

15 Hour formula for fall: =IF(Term@row = "F", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

15 Hour formula for spring: =IF(Term@row = "S", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

20 Hour formula for fall: =IF(Term@row = "F", INDEX(IF(Hrs@row = 20, {UpdateRates 20Hrs}, {15 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

20 Hour formula for spring: =IF(Term@row = "S", INDEX(IF(Hrs@row = 20, {UpdateRates 20Hrs}, {15 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

But I'm still getting Invalid ref. The rates page has not changed.

Paul Newcome

Right. You do not need 4 separate formulas. There is no need to break out the 15 hours and 20 hours into separate columns. The formula I provided already accounts for that using the bold portion:

=IF(Term@row = "F", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

That IF statement is telling the formula to look in either the 15 hour column of the reference sheet or the 20 hour column of the reference sheet depending on what is in Hrs@row. The issue is that the {20 Hour Column} cross sheet reference has not yet been created in your sheet.

maineL

YOU ARE AMAZING!!!! I'm a dope. I'm sorry it was right there the whole time!!! THANK YOU SO MUCH! This system is making my life a LOT more efficient! Thanks again! I'm sure I'll come up with more questions, but will start under a new thread! THANK YOU SO MUCH!

Paul Newcome

Happy to help. 👍️