Index formula versus If And

maineL

Ok so here is what I have now:

15小时的形式ula for fall: =IF([email protected]= "F", INDEX(IF([email protected]= 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH([email protected], {UpdateRates Term}, 0)))

15小时的形式ula for spring: =IF([email protected]= "S", INDEX(IF([email protected]= 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH([email protected], {UpdateRates Term}, 0)))

20 Hour formula for fall: =IF([email protected]= "F", INDEX(IF([email protected]= 20, {UpdateRates 20Hrs}, {15 Hour Column}), MATCH([email protected], {UpdateRates Term}, 0)))

20 Hour formula for spring: =IF([email protected]= "S", INDEX(IF([email protected]= 20, {UpdateRates 20Hrs}, {15 Hour Column}), MATCH([email protected], {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([email protected]= "F", INDEX(IF([email protected]= 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH([email protected], {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[email protected]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.️