Excel - 基于另一张表的查找值突出显示行 [英] Excel - Highlight Rows based on look up value from another sheet
问题描述
< br>
在另一张表中有几个查找ups ..我需要查找那些,并根据查找列表突出显示不同的颜色。
例如,在上述情况下,我需要公司A在Oone颜色和C公司在另一个。
如果突出显示是挑战,我不介意即使在旁边有一个单独的coloumn,说如果它是一个合作伙伴或竞争对手。
我在条件格式化下尝试了下面的公式,但不适用于不同的颜色...它颜色与所有相同
= NOT(ISNA(VLOOKUP(B1,'Lookup List'!$ A $ 2:$ D $ 200,1,FALSE)))
请尝试选择公司名称列和HOME>样式 - 条件格式,新规则..., 使用公式确定要格式化的单元格和格式在此公式为真的值::
= COUNTIF('Lookup List'!$ A:$ A,B1)
格式化... ,选择颜色填充(高亮),选择确定,确定。
然后重复:
= COUNTIF('Lookup List'!$ B:$ B,B1 )
填充不同。
如果每个条目是一种颜色或另一种颜色,那么一个CF规则就足够了r颜色作为标准填充。
Hi I have an excel sheet, that has a column called as Company name
in another sheet there is there are couple of look ups.. i need to look up those and highlight them in different colors based on the look up list.
for example, in the above scenario, i need Company A Highligted in oone color and company C in another.
If highlighting is challgeng, i dont mind even having a seperate coloumn next to it that says, if it is a partner or competitor.
i tried the below formula under conditional formatting .. but doesnt work for different colours... it colors everything the same
=NOT(ISNA(VLOOKUP(B1,'Lookup List'!$A$2:$D$200,1,FALSE)))
Please try selecting Company Name column and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::
=COUNTIF('Lookup List'!$A:$A,B1)
Format..., select colour Fill (highlight) of your choice, OK, OK.
Then repeat with:
=COUNTIF('Lookup List'!$B:$B,B1)
with a different fill.
If every entry is one colour or the other then one CF rule is sufficient, with the other colour applied as standard fill.
这篇关于Excel - 基于另一张表的查找值突出显示行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!