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

04/20/21
Answered - Pending Review

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([email protected], {Contract Management - Vendor}, 0))), ISDATE(INDEX({Blanket PO | EXE}, MATCH([email protected], {Blanket PO | Vendor}, 0)))), 1, 0)

=IF(ISDATE(OR(INDEX({Contract Management - contract exe}, MATCH([email protected], {Contract Management - Vendor}, 0)), INDEX({Blanket PO | EXE}, MATCH([email protected], {Blanket PO | Vendor}, 0)))), 1, 0)

This formula WORKS when referencing 1 sheet

=IF(ISDATE(INDEX({Contract Management - Sub notification}, MATCH([email protected], {Contract Management - Vendor}, 0))), 1, 0)

Answers

  • Bassam KhalilBassam 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]

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Paul NewcomePaul 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([email protected], {Contract Management - Vendor}, 0))


    Second Sheet:

    =INDEX({Blanket PO | EXE}, MATCH([email protected], {Blanket PO | Vendor}, 0))


    Combining with IFERROR:

    =IFERROR(first_sheet, second_sheet)


    =IFERROR(INDEX({Contract Management - contract exe}, MATCH([email protected], {Contract Management - Vendor}, 0)), INDEX({Blanket PO | EXE}, MATCH([email protected], {Blanket PO | Vendor}, 0)))

Sign In or Register to comment.