Forum Discussion
Excel Formula
Hello!
I'm hoping someone could help me to create a formula based on the following information:
List of ranges:
Apples = 1-49
Oranges = 50-59
Grapes = 60-69
Melons = 70-79
Bananas = 80-89
Pineapples = 90-98
Strawberries = 99
The goal - a user inputs the fruit type in column A and a value in column B. Based on the fruit type (the fruit types are the main focus) specified in column A, the formula validates the value specified in column B is within the respective range. If it's not then highlight it in RED to indicate it failed. If it is then highlight it in Green to indicate it passed.
For example, a user specifies "Pineapples" in A2 and "100" in B2. Since "Pineapples" is the fruit of interest, then we would need to check that B2 is within the "Pineapples" range of 90-98. Since it's not then, we would need to highlight it RED.
Please see the attached spreadsheet for clarification.
I suggest following rule for conditional formatting:
=AND(VLOOKUP(A2,$G$3:$I$9,3,TRUE)>=B2,VLOOKUP(A2,$G$3:$H$9,2,TRUE)<=B2)
To apply this formula you have to sort the table containing your ranges in ascending order. Please compare attached file.
If you want to highlight non matching values in addition i would enter additional rule for formatting:
=NOT(AND(VLOOKUP(A2,$G$3:$I$9,3,TRUE)>=B2,VLOOKUP(A2,$G$3:$H$9,2,TRUE)<=B2))
3 Replies
I suggest following rule for conditional formatting:
=AND(VLOOKUP(A2,$G$3:$I$9,3,TRUE)>=B2,VLOOKUP(A2,$G$3:$H$9,2,TRUE)<=B2)
To apply this formula you have to sort the table containing your ranges in ascending order. Please compare attached file.
If you want to highlight non matching values in addition i would enter additional rule for formatting:
=NOT(AND(VLOOKUP(A2,$G$3:$I$9,3,TRUE)>=B2,VLOOKUP(A2,$G$3:$H$9,2,TRUE)<=B2))
- That worked! Thank you!
IF(ISERROR(LOOKUP(2,1/((A2=$H$3:$H$9)*(B2>=$I$3:$I$9)*(B2<=$J$3:$J$9)),$H$3:$H$9)),"Fail","Passed")
