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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!