Conditional Formatting & VLOOKUP

[VickiR]

Hi, I can't seem to get this to work. Any assistance would be greatly appreciated. I am trying to use conditional formatting on sheet 1 to highlight green any sales months that the seller was over goal based on their goal data on sheet 2. Sheet two contains the seller's goal data. I need to do a vlookup to find the sellers on sheet 2 to identify their goal. There are hundreds of sellers and they are not in order. This is what I came up with and it doesn't seem to work: =C5>VLOOKUP($B$5,GOAL!$B$5:$C$53,2,FALSE)

Sheet 1 (Seller Sales)

	Day1	Day2	Day3	Day4	Day5	Day6
Seller 1	14,275	43,816	15,251	7,738	11,646	8,814
Seller 2	6,652	7,881	9,880	3,261	6,724	7,595
Seller 3	11,560	8,301	2,552	12,167	2,472	13,412

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

Sheet 2 (Seller Goal)

	GOAL
Seller 1	8,083
Seller 10	10,044
Seller 5	10,730

<colgroup><col><col></colgroup><tbody>
</tbody>

Thank you very much,

 

[DanteAmor]

Assuming your data starts in cell B2
Follow the next steps:
- In sheet2 "Goal" select the columns of sellers and their goal, for example A and B.
- In the name box type sellers and press enter
- In the conditional format, put the following formula
=B2 > =VLOOKUP($A2, sellers, 2,0)
- In applies to
=$B$2:$G$1000

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">Day1</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">Day2</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">Day3</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">Day4</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">Day5</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; ">Day6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Seller 1</td><td style="background-color:#92d050; text-align:right; ">14,275</td><td style="background-color:#92d050; text-align:right; ">43,816</td><td style="background-color:#92d050; text-align:right; ">15,251</td><td style="background-color:#fafafa; text-align:right; ">7,738</td><td style="background-color:#92d050; text-align:right; ">11,646</td><td style="background-color:#92d050; text-align:right; ">8,814</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Seller 2</td><td style="background-color:#fafafa; text-align:right; ">6,652</td><td style="background-color:#fafafa; text-align:right; ">7,881</td><td style="background-color:#92d050; text-align:right; ">9,880</td><td style="background-color:#fafafa; text-align:right; ">3,261</td><td style="background-color:#fafafa; text-align:right; ">6,724</td><td style="background-color:#fafafa; text-align:right; ">7,595</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">Seller 3</td><td style="background-color:#fafafa; text-align:right; ">11,560</td><td style="background-color:#fafafa; text-align:right; ">8,301</td><td style="background-color:#fafafa; text-align:right; ">2,552</td><td style="background-color:#92d050; text-align:right; ">12,167</td><td style="background-color:#fafafa; text-align:right; ">2,472</td><td style="background-color:#92d050; text-align:right; ">13,412</td></tr></table>

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2 GOAL</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">SELLER</td><td style="background-color:#ffff00; ">GOAL</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Seller 1</td><td style="text-align:right; ">8,083</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Seller 10</td><td style="text-align:right; ">10,044</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Seller 5</td><td style="text-align:right; ">10,730</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Seller 2</td><td style="text-align:right; ">9,000</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Seller 3</td><td style="text-align:right; ">12,000</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td></tr></table>

 

[DanteAmor]

Additional Information


On Sheet1

---

On sheet1, add conditional format, select cell B2:

 

[VickiR]

Thank you for all your help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.