IF statement within an Index Match

I would like to have a formula on one sheet that looks to another sheet for the following.

On one sheet it lists all the employee IDs with a "Module 1 Date" column and/or a "Module 1 Makeup Date" - if they missed the "Module 1 Date" (so the employees that missed Module 1 would have two dates ). I have another sheet that lists all the employees that I would like to pull over these dates for - but just the date they actually attended - so for those with a "Module 1 Makeup Date" pull that over and for those with just the "Module 1 Date" pull that.

So I would like to Index Match to go look on the other sheet and if the employee has a "Module 1 Makeup Date", pull that over but if this is blank, pull over the "Module 1 Date".

I have tried a bunch of different ways but can't seem to figure it out:

=IF(INDEX({Module 1 Makeup Date}, MATCH([Name - UID]@row, {Name - UID}, 0)), INDEX({Module 1 Date}, MATCH([Name - UID]@row, {Name - UID}"))

Any help is greatly appreciated!! Thank you!!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are actually very close. Her eis the logic you would want...


    =IF(INDEX/MATCH for Makeup Date <> "", INDEX/MATCH for Makeup Date, INDEX?MATCH for Original Date)


    =IF(INDEX({Module 1 Makeup Date}, MATCH([Name - UID]@row, {Name - UID}, 0)) <> "", INDEX({Module 1 Makeup Date}, MATCH([Name - UID]@row, {Name - UID}, 0)), INDEX({Module 1 Date}, MATCH([Name - UID]@row, {Name - UID}, 0)))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!