add an IF statement to an INDEX MATCH
Hi,
I am using an INDEX MATCH equation to pull a Unique ID from my Master Contract Data sheet. The equation works well like this:
=IFERROR(INDEX({Contract Master Data - Contract Unique ID}, MATCH([Contractor Name]@row, {Contract Master Data - Contractor Name}, 0)), "No Match Found")
However, I would now like to add an IF statement to check the Master Contract Data sheet to see if the column named 'Contract Closed?' is checked. If checked, the INDEX MATCH should fail. If not checked, the INDEX MATCH should proceed.
Your guidance is much appreciated!
Best Answer
-
This should do the trick; you will just need to add a new cross sheet reference: {Contract Master Data - Contract Closed?}
=IFERROR(IF(INDEX({Contract Master Data - Contract Closed?}, MATCH([Contractor Name]@row, {Contract Master Data - Contractor Name}, 0)) = 1, "No Match Found", INDEX({Contract Master Data - Contract Unique ID}, MATCH([Contractor Name]@row, {Contract Master Data - Contractor Name}, 0))), "No Match Found")
Answers
-
This should do the trick; you will just need to add a new cross sheet reference: {Contract Master Data - Contract Closed?}
=IFERROR(IF(INDEX({Contract Master Data - Contract Closed?}, MATCH([Contractor Name]@row, {Contract Master Data - Contractor Name}, 0)) = 1, "No Match Found", INDEX({Contract Master Data - Contract Unique ID}, MATCH([Contractor Name]@row, {Contract Master Data - Contractor Name}, 0))), "No Match Found")
-
Thanks that worked perfectly!
-
Awesome, I'm glad I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!