Forum Discussion
Excel Formula
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))
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")
