Search for Value in Excel Return Yes or No Based on Multiple Criteria

[mkf001]

Hello!

I have 2 reports that are on separate sheets, 1 & 2. Both contain a load id # and I would like to enter a formula on Sheet 1 that tells me whether or not that load id # is on sheet 2, returning either a yes or no value. To achieve this, I have the following formula:
=IFERROR(IF(Match(F5,'Sheet 2!$C:$C,0),"Yes",),"No"). The formula works, however, on Sheet 1, column I shows when each load id # was shipped. I would like the above formula to only give me a yes or no response if the ship date is between a start and ending date which I manually type into cells D9 and D10 on Sheet 1. If it is not within the date range specified, I would like the formula to return a blank. If it is in the date range specified, but not on Sheet 2, then I would like it to return No and if it is within the date range and is on Sheet 2 I would like it to return a Yes. Is there a way to do this all in one formula?

Thanks in advance for any help provided and I apologize for any errors. I am having trouble typing in the message box for some reason. :/

 

[Eric W]

How about:

=IF(OR(I5< $D$9,I5>$D$10),"",IF(COUNTIF(Sheet2!C:C,F5)=0,"No","Yes"))

 

[mkf001]

Thank you for the response! It appears to be working for the most part with the exception of 2 lines. Could you explain the logic or what the formula is doing at each step so that I can try to troubleshoot why 2 lines are not returning the expected values?

Thanks so much for your help!

 

[Eric W]

Sure:

=IF(OR(I5< $D$9,I5>$D$10),"",IF(COUNTIF(Sheet2!C:C,F5)=0,"No","Yes"))

If I5 is your ship date, and D9 is the start date of your range, and D10 is the end date of your range, then I5< $D$9 is checking to see if the ship date is before the start of your range, and I5 > $D$10 is checking to see if the ship date is after the end of your range. If either of those is true (the OR), then it's outside your range and you want a blank. This is the most likely place for an error. Date values can also contain a time value which may not display, so if I5 is 7/1/2018 14:00, and D10 is just 7/1/2018, then it will probably erroneously reject it. Also, if any of the dates are empty, you could get bad results.

If neither of those is true, then we search Sheet2 for a match. COUNTIF counts how many times F5 is found in column C. If F5 is never found, then the result is 0, and we put in "No", otherwise "Yes". In this situation, I like COUNTIF over MATCH because you don't need the IFERROR, and because COUNTIF is sensitive to the actual range, so it's more efficient.

 

[mkf001]

Thank you for your help and the explanation! I must have initially mistyped it yesterday because when I retyped it with fresh eyes this morning the formula is working perfectly. The explanation helps me understand how it all pulls together.

 

[Eric W]

I'm happy it works for you!