多列vlookup条件格式 [英] Multi-column vlookup conditional formatting
问题描述
表格1：
$ b我希望根据几件事情为工作表创建条件格式。 $ b
参考：Date= Col1 Row1
日期人口普查RN的日程表
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
表2：
参考：Census= Col1 Row1
人口普查RN人员需求
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
所以这里的问题。
我在表1中获得了这些值，我想与表2进行比较，并使用条件格式来突出显示某些事物。我需要从第一页看待人口普查（作为参考，1-Oct人口普查是8 @ 7a），并在第二页找到。然后，我需要它将 RN的计划中的号码与 RN人员配置需求中的号码进行比较（RN人员需求，在这种情况下为6 @ 7a; RN的附表8 ）。当从工作表1（从1-Oct的情况下的F3）的RN的计划值比工作表2中的人员配备需求高时，需要一个条件格式来强调该值为绿色。当它是LOWER我需要它来突出显示该值黄色。当有一个完全匹配没有什么需要发生。
我已经尝试过vlookups和索引/匹配，嵌套的IF语句和IFERROR等...这个有点超出了我的实践范围。任何帮助将非常感谢!!
我采取了下面的方法，但是您可以更多地巩固它。 p>
编辑：我能够整合更多。
将此公式应用于Sheet1中Range（E3：E9）的条件格式。然后制定一个附加规则，但将公式更改为> 并填充绿色。然后，您可以对列F和G使用相同的两种格式，您只需更改公式中的列引用和vlookup列指示器。
因此，对于列F，公式将为 = F3< vlookup（C3，Sheet2！$ A：$ D，3,0）
在这里提供更多的更详细的理解方法
Sheet1
输入单元格I3中公式中看到的公式，并拖动范围（K9）的结尾
条件格式
E3：G9并在条件公式规则法则中应用显示的公式蒙古包。
对于参考，我的sheet2看起来像这样（根据您显示的数据）。
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!!
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).
这篇关于多列vlookup条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
