Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulamatchnested-if

How to find value using INDEX + MATCH with SETS of multiple criteria on Google Sheets?

The formula works if I compare exact days, but it's possible that the person filled out a form today and will only fill out the other the next day, so it's important to test for a couple of days ahead and behind anyway.

This is the working formula for the same day:

=if(INDEX(F:F,MATCH(1,(H:H=C3)*(DAY(LEFT(F:F,10))=DAY(LEFT(A3,10))),0))<>"","Yes","No")

This is my attempt to make it work for 1 and 2 days later:

=IF(OR(
if(INDEX(F:F,MATCH(1,(H:H=C3)*(DAY(LEFT(F:F,10))=DAY(LEFT(A3,10))),0))<>""),
if(INDEX(F:F,MATCH(1,(H:H=C3)*(DAY(LEFT(F:F,10))=DAY(LEFT(A3,10))+1),0))<>""),
if(INDEX(F:F,MATCH(1,(H:H=C3)*(DAY(LEFT(F:F,10))=DAY(LEFT(A3,10))+2),0))<>"")),
,"Yes","No")

Here's a sample of the file, in case you feel like spreading your expertise.

Appreciate you!

Solution

  • you have 2 commas there. remove one:

    enter image description here