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
-
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!
Answers
-
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!
-
This worked!! THANK YOU SO MUCH!!!
-
Happy to help. 👍️
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 306 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!