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

Natalia Kataoka

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)

Bassam Khalil

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 : Bassam.k@mobilproject.it

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)))