Excel 2007 : Combining vlookup and if statements to return multiple criteria

**rnomis** · 09-07-2011, 11:11 AM

I have a spreadsheet which filters on various criteria based on location and contract renewal dates etc.
Previously, I had a simple if statement to filter on usage criteria: =IF(AY2>=2000, "GOOD", IF(AY2>=1000, "OK", IF(AY2>=0.1, "BAD", IF(AY2 >0.1, "BAD", "Nil usage" ) ) ) )

(not pretty but it worked)

I now find I need to create a more accurate filter based on other numerical criteria i.e. more than 500 hours and more than 5 tasks set and Data sent in would return GOOD
I attach an example of what I want with the data headings and rules:

HTML Code:

I also attach an example sheet as above. I hope someone can help.

Thanks
Simon

**martindwilson** · 09-07-2011, 04:35 PM

maybe
=IF(C2="n","bad",IF(AND(D2>500,E2>=5,F2>=5,G2>=3),"good",IF(AND(D2>100,E2>=3,F2>=3,G2>=2),"ok","bad")))

**rnomis** · 09-07-2011, 06:01 PM

Great, works well. Just one thing to finish it off.
I now find that each week, I enter new data and the number of records may vary, there maybe some blank records at the bottom which will be marked as BAD using this formula. Is there any way I can make those cells return nothing so when I filter them, blank records are not shown or add a blank criteria? i.e. "" or NO DATA
Hope this makes sense and thanks for the quick response.

Just to be even more adventurous.... I have now referenced the cells along the lines of Hgood, Hok, Lgood etc. It got me thinking. Each quarter, I will need to edit the data table as the figures are cumulative. Wouldn't it be great if I could use the date function to change the values of the referenced cells using the TODAY() function and setting up a table showing the start and end dates of each quarter. Each referenced cell would then change automatically when the new quarter started.

Just a thought....