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
-
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
bassam.khalil2009@gmail.com
☑️ 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"
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!