How to Nest IF statements in the correct order

Options

I am tasked with collecting labor rates per employee, but the labor rates change based on the dates posted. For instance, I have old labor rates applied to dates older than December 1st 2023. We recently had some promotions and now I also have to pull labor rates after March 28th 2024. The labor rates after March 28th are not pulling accurately - they show up as the Q1 rates...

I need help with nesting these if statements in the correct order.

=IF(Date@row > DATE(2024, 3, 28), INDEX({Labor Rates | Internal Uptd 24}, MATCH([Employee Name]@row, {Labor Rates | Employee}), 0), IF(Date@row < DATE(2023, 12, 1), INDEX({Labor Rates | Internal Rate 23}, MATCH([Employee Name]@row, {Labor Rates | Employee}), 0), INDEX({Labor Rates | Internal Q1 2024}, MATCH([Employee Name]@row, {Labor Rates | Employee}), 0)))

Please let me know if I need to provide more information. Thanks in advance for any consideration!

Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Try specifying "0" as the search type for your match conditions:

    =IF(Date@row > DATE(2024, 3, 28), INDEX({Labor Rates | Internal Uptd 24}, MATCH([Employee Name]@row, {Labor Rates | Employee}, 0), 0), IF(Date@row < DATE(2023, 12, 1), INDEX({Labor Rates | Internal Rate 23}, MATCH([Employee Name]@row, {Labor Rates | Employee}, 0), 0), INDEX({Labor Rates | Internal Q1 2024}, MATCH([Employee Name]@row, {Labor Rates | Employee}, 0), 0)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!