Conditional formating based on reference table

[kaver22]

I'm trying to develop an excel spreadsheet to use for writing work schedules. The current set-up I have is columns A, B, and C are "employee", "status", and "hours",respectively. Off to the side (columns K, L, M) I have a table that designates the range of hours allowed to each employment status. It's three columns: "status", "min", max".

What I want to do is conditionally format the "hours" column so if the number of scheduled hours falls within the allowed range for that employees status from column B (based on the reference table) the cell will turn green. I would like for it to turn red if it falls outside of that range.

So, I want to match column B "status" to column K "status" in the table and if the column C "hours" falls between the "min" and "max" (col. L and M in table) then the "hours" cell turns green, otherwise, it turns red.

For example: Employee D's status is RFT, currently scheduled for 28 hrs. In the table RFT status is a minimum 30 hours, maximum 45 hrs. So, because D's hours are not >= "min" and <="max" the cell should be red. If D's hrs, however, were at 32, the cell should be green.

I hope this all makes sense. I've never posted to a board for this kind of help and trying to describe the problem without the visual is more difficult than I thought.

Thanks!!

 

[Fluff]

Select C2 to the last used row in col C & use this formula
=AND(C2<=INDEX($M$2:$M$4,MATCH(B2,$K$2:$K$4,0)),C2>=INDEX($L$2:$L$4,MATCH(B2,$K$2:$K$4,0)))