Nested IF AND OR Functions

[lswobx]

I am working on a fairly simple project but have hit a roadblock in my Excel knowledge. I want to track oil change and tire rotation for a fleet of vehicle. 

I record mileage at last oil change, tire rotation and current mileage. I want to be able to quickly see which vehicle needs service based on two triggers, 5000 >= oil and 10,000 >= tires. 

IF Current Miles - Miles Last Oil Change >=500 AND Current Miles - Miles Last Rotation <=10,000, OIL ONLY, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change >=4500 AND Current Miles - Miles Last Rotation <=10000, OIL SOON, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change <=5000 AND Current Miles - Miles Last Rotation >=9500, ROTATE SOON, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change <=5000 AND Current Miles - Miles Last Rotation >=10,000, ROTATE ONLY, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change >=5000 AND Current Miles - Miles Last Rotation >=10,000, OIL & ROTATE, NO SERVICE

I think this explains what I am trying to do. I want a "flag" when oil & tires are within 500 miles of their due date, a "do now" when it's either just oil or just rotate and a "do now" when it is both. I have tried nesting the IF, AND, OR statements and have ended up with a hot mess.

Thanks so very much for any help you can provide! Linda Williams

[#2]

@lswobx 

How about using a lookup table? See the attached version. You can change the text in the lookup table, of course.

[#3 · Accepted solution]

@lswobx

You didn't really say anything about how your current data is laid out. But, if it were me, I would set up separate fields for the oil and tire service to try to make it easier to work with and filter.

Attached is an example that I hope will be helpful. 

[#4]

You are a ROCK STAR! This is exactly what I was looking to accomplish. Thank you so very very much for your help.