INDEX MATCH AND formula assistance

I currently have a formula to determine the day of the week when a date is selected, which works perfectly.

=IF(WEEKDAY([Overtime Date]@row) = 1; "Sunday"; IF(WEEKDAY([Overtime Date]@row) = 2; "Monday"; IF(WEEKDAY([Overtime Date]@row) = 3; "Tuesday"; IF(WEEKDAY([Overtime Date]@row) = 4; "Wednesday"; IF(WEEKDAY([Overtime Date]@row) = 5; "Thursday"; IF(WEEKDAY([Overtime Date]@row) = 6; "Friday"; IF(WEEKDAY([Overtime Date]@row) = 7; "Saturday")))))))

I have added a checkbox which should be selected if the day is a public holiday.

The index match references the table below to determine the amount it should be multiplied by.

I need to change the formula to include that if the Public Holiday box is selected, the amount to multiply by should be 2,0.

I do not know if the INDEX MATCH is the correct formula for this, or whether I should rather be using a VLOOKUP IF formula.

Any assistance will be appreciated.


Michelle Basson

Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!