Conditional INDEX/MATCH Question
Hi there,
I am using the following formula to pull data from a second sheet but realized when "Medicine" is selected in the Entity/Dept column, I would need to match information from a Division column instead.
=INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0))
I thought a conditional Index/Match might help so I tried this but received an unparseable error:
=INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}), IF(JHU Entity/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, MATCH([JHU Department of Medicine Division]@row, {Division}, 0))
The column headers are below:
Could someone point me in the right direction?
Thanks,
Ali
Best Answer
-
I think you just need to move where the condition is coming from. You first need to do the IF, and then have 2 different INDEX/MATCH formulas based on the return. This should work:
=IF([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, INDEX({Notice Name}, MATCH([JHU Department of Medicine Division]@row, {Division}, 0)), INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0)))
Answers
-
I think you just need to move where the condition is coming from. You first need to do the IF, and then have 2 different INDEX/MATCH formulas based on the return. This should work:
=IF([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, INDEX({Notice Name}, MATCH([JHU Department of Medicine Division]@row, {Division}, 0)), INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0)))
-
That worked great. Thank you!
-
No problem, glad it is all working
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!