Multi-column vlookup conditional formatting

Question

I'm looking to create a conditional format for a worksheet based on a couple of things.

Sheet 1:

Reference: "Date" = Col1 Row1

    Date    Census          RN's On Schedule                
            7a  3p  11p     7a  3p  11p     
    1-Oct   8   8   9       8   8   9       
    2-Oct   10  14  13      13  16  13      
    3-Oct   10  12  12      12  12  10      
    4-Oct   12  10  10      11  11  10      
    5-Oct   10  11  11      11  12  11      
    6-Oct   10  11  14      11  12  12      
    7-Oct   13  12  12      12  14  12      

Sheet 2:

Reference: "Census" = Col1 Row1

Census  RN Staffing Needs       
        7a  3p  11p     
1       2   2   2       
2       2   2   2       
3       2   2   2       
4       3   3   2       
5       4   4   3       
6       4.5 5   5       
7       5   6   6       
8       6   7   6.5     
9       7   8   7       
10      8.5 9   8.5     
11      10  11  10      
12      12  12  11.5    
13      12  12  12      
14      12  12  12      
15      13  13  13      
16      13  13  13      
17      14  14  14      
18      14  14  14      

So here's the question.

I've got these values in sheet 1 that I'm looking to compare to sheet 2 and use conditional formatting to highlight certain things. I need excel to look at the census (for reference, 1-Oct census is 8 @ 7a) from sheet one and find it in sheet two. Then I need it to compare the number in RN's On Schedule to the number in RN Staffing Needs (RN Staffing Needs, in this case 6 @ 7a; RN's On Schedule, 8). When the RN's on Schedule value from Sheet 1 (F3 in the case of 1-Oct) is HIGHER than the RN Staffing Needs from Sheet 2 I need a conditional format to highlight that value green. When it is LOWER I need it to highlight that value yellow. When there is an exact match nothing needs to happen.

I've tried vlookups and index/matches with nested IF statements and IFERROR's and the like... this one's a little out of my scope of practice. Any help will be greatly appreciated!!

Solution

I took the approach below, but you can probably consolidate it even more.

EDIT: I was able to consolidate it even more.

Apply this formula for conditional formatting for the Range(E3:E9) in Sheet1. Then make an additional rule, but change the formula to > and to fill green. Then you can use the same two formats against columns F and G and you just need to change the column references in your formula and the vlookup column indicator.

So, for column F, the formula would be =F3<vlookup(C3,Sheet2!$A:$D,3,0)

LEAVING OLD METHOD IN HERE FOR MORE DETAILED UNDERSTANDING

Sheet1

Enter the formula seen in the formula in cell I3 and drag through the end of the range (K9)

Conditional Formatting

Select the range from E3:G9 and apply the displayed formulas in the Conditional Formula Rules Manager.

For reference my sheet2 looks like this (based on your data displayed).

Comments

[mdaube]

=IF(E9>VLOOKUP(B9,'SICE Guide'!$A:$D,L$1,0),"GREEN",IF(E9<VLOOKUP(B9,'SICE Guide'!$A:$D,L$1,0),"YELLOW","")) My L$1 is your I$1, there's an additional data set. So what is the I$1 referenced for? I just want to be able to explain the function and how it's working... btw it's working, I'm going to input more data and see what I get. :)

[mdaube]

Both methods worked! You're a life saver Scott! Now I just have to apply this to 38 other sheets and back fill three months worth of data!... See you in December when I come up for air! :P

[Scott Holtzman]

Great! Glad it worked. Good luck with the dredging ... The L$1 is referenced to get the vlookup column count. You can see I wrote 2 in that column (and 3, 4 in the columns next to it!)

[mdaube]

Thanks again! Couldn't have done it without ya Scott!