Combining 2 ISDATE(INDEX(MATCH arguments referencing 2 reference sheets

I am creating a new vendor request form that will copy rows to two separate, detailed sheets where I can track their unique requirements. From my detailed sheets, I want some information to flow back to the request sheet so those that requested can view the progress. What I want to do is to check a box should dates be entered in certain fields. I have figured out the formula referencing 1 sheet, but am stumped at how to duplicate and combine the formula so it looks at both sheets. I am getting #NOMATCH errors.

New Vendor Request Form

I would like the above check box "Agreement on File" to be checked if there are dates in the corresponding fields in my below reference sheets (PO EXE or Contract Execution Date):

I have tried a couple iterations of the formula with no luck:

=IF(OR(ISDATE(INDEX({Contract Management - contract exe}, MATCH(Vendor@row, {Contract Management - Vendor}, 0))), ISDATE(INDEX({Blanket PO | EXE}, MATCH(Vendor@row, {Blanket PO | Vendor}, 0)))), 1, 0)

=IF(ISDATE(OR(INDEX({Contract Management - contract exe}, MATCH(Vendor@row, {Contract Management - Vendor}, 0)), INDEX({Blanket PO | EXE}, MATCH(Vendor@row, {Blanket PO | Vendor}, 0)))), 1, 0)

This formula WORKS when referencing 1 sheet

=IF(ISDATE(INDEX({Contract Management - Sub notification}, MATCH(Vendor@row, {Contract Management - Vendor}, 0))), 1, 0)

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 04/21/21

    Hi @Natalia Kataoka

    Hope you are fine if you can share me as an admin on a copy of those sheets after you remove any sensitive data i will create the exact formula for you.

    My Email : [email protected]

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try incorporating an IFERROR.


    Run the first INDEX/MATCH. If there is no match, it will generate an error. That's when we use the IFERROR to say that if there is an error (no match) on the first, then run the second INDEX/MATC.

    First Sheet:

    =INDEX({Contract Management - contract exe}, MATCH(Vendor@row, {Contract Management - Vendor}, 0))


    Second Sheet:

    =INDEX({Blanket PO | EXE}, MATCH(Vendor@row, {Blanket PO | Vendor}, 0))


    Combining with IFERROR:

    =IFERROR(first_sheet, second_sheet)


    =IFERROR(INDEX({Contract Management - contract exe}, MATCH(Vendor@row, {Contract Management - Vendor}, 0)), INDEX({Blanket PO | EXE}, MATCH(Vendor@row, {Blanket PO | Vendor}, 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!