How to return a result when searching multiple criteria including date range

[Jonnyd74]

I am trying to return a value when searching multiple criteria including a date range. For example-
Column A= Time of Error
Column B= Description of Error
Column C= Would like Return of Error Message 500 only during a certain time.

A1 05/10/2016 12:00 B1- Customer Receives Error 500
A2 05/10/2016 1:00 B2- Customer Receives Error 500
A3 05/10/2016 2:00 B3- Customer Receives Error 500
A4 05/10/2016 3:00 B4- Customer Receives Error 500

In Column C, I would like it to return "Customer Receives Error 500" only if it was between 5/10/2016 1:00 and 5/10/2016 2:00, otherwise it would return "Different Issue"

This will be an ever changing spreadsheet with data constantly added with other errors, so would prefer to avoid Advance Filtering.



Thanks much!

 

[Jonnyd74]

Here is a detailed scenario-


There was a Outage between 12-3 that could have resulted in the 1 of the 3 errors ("Error 500", or "Connection Lost Error" or "Error 503") below. If the error occurred outside of the 12-3 time frame, it was another issue. I would like to return a value of "Error Occurred due to Global Product Outage" for anytime 1 of the 3 errors occurred between 12-3.


A1 05/10/2016 12:00 | B1- Customer Receives "Error 500"
A2 05/10/2016 1:00 | B2- Customer Receives "Connection Lost Error"
A3 05/10/2016 2:00 | B3- Customer Receives "Oops Error"
A4 05/10/2016 3:00 | B4- Customer Receives "Error 503"
A5 05/10/2016 4:00 | B5- Customer Receives "Error 500"
A6 05/10/2016 5:00 | B6- Customer Receives "Error 500"

 

[Joe4]

Try this:

=IF(AND(A1>=$Y$1,A1<=$Z$1,OR(ISNUMBER(SEARCH("Error 500",B1)),ISNUMBER(SEARCH("Error 503",B1)),ISNUMBER(SEARCH("Connection Lost Error",B1)))),"Outage Error","Different Issue")

 

[Jonnyd74]

Yes!!! That is the magical formula, it worked perfectly! Thanks a mill Joe4, that saved me a ton of time.

 

[Joe4]

You are welcome.
Glad I could help!

 

[Jonnyd74]

Apologies for opening this up again, but I have to take one more factor into account. I'll start with my original data and a Column C for "Region"

A1 05/10/2016 12:00 | B1- Customer Receives "Error 500" | C1 UK
A2 05/10/2016 1:00 | B2- Customer Receives "Connection Lost Error"|C2 UK
A3 05/10/2016 2:00 | B3- Customer Receives "Oops Error"| C3 US
A4 05/10/2016 3:00 | B4- Customer Receives "Error 503" | C4 UK
A5 05/10/2016 4:00 | B5- Customer Receives "Error 500"| C5 US
A6 05/10/2016 5:00 | B6- Customer Receives "Error 500"| C6 US

Similar scenario only this time I would like to add a 3rd Qualifier of Region (Column C), so it would go like this-

There was a Outage between 12-3 that could have resulted in the 1 of the 3 errors ("Error 500", or "Connection Lost Error" or "Error 503") only in the UK. If the error occurred outside of the 12-3 time frame in the UK or if the error occurred between 12-3 in the US, then it was another issue. I would like to return a value of "Error Occurred due to UK Product Outage"

 

[Joe4]

Note that you can keep adding conditions to your AND clause (I believe up to 32 conditions in most version of Excel).
So just add another checks column C, i.e.

C1="UK"

I will leave it to you to add it into the existing formula. It should be pretty straightforward, and will be a good test to make sure you understand how this all works.