Use VLOOKUP and conditional formatting?

[Bow To The Robots]

Hello,

I am new to the forum and would describe myself as a moderately-knowledgeable Excel user. Here's what I need to accomplish:

I have two data sets, one is my top-100 SKUs, and the other is my backorder report. What I want to do is color-code the cells on the BO report that correspond to my top-100.

Help?

Thank you in advance!

[Tbez]

Hi Bow to The Robots,

I might have a solution for that. Here is how:

1. Use a vlookup in the rearmost column of the BO report and make it return 1 if the item is on the top 100 list and nothing if the item is not on the list. Hide the column so your sheet will look better.

2. Use conditional formatting to format the cells in the rows that has 1 in the hidden column.

I write in norwegian, but I've tried to translate the vlookup-code below.

Please Login or Register  to view this content.

The conditional formatting is defined by a formula.

Please Login or Register  to view this content.

You might have to edit the rule in order to get rid of $ signs and "" signs.

[Bow To The Robots]

Hi Bow to The Robots,

I might have a solution for that. Here is how:

1. Use a vlookup in the rearmost column of the BO report and make it return 1 if the item is on the top 100 list and nothing if the item is not on the list. Hide the column so your sheet will look better.

2. Use conditional formatting to format the cells in the rows that has 1 in the hidden column.

I write in norwegian, but I've tried to translate the vlookup-code below.

Please Login or Register  to view this content.

The conditional formatting is defined by a formula.

Please Login or Register  to view this content.

You might have to edit the rule in order to get rid of $ signs and "" signs.

Thanks, I'll try it!

[FDibbins]

An easier way...
use "Use Formula"

=vlookup(B1,top-10-list-range,1,0)
where B1 is a cell in your BO list

format fill as needed

[Tbez]

Hi FDibbins. Are you saying you can use vlookup directly in the conditional formatting window?

If that is so, Excel just got even better I can't wait to try it out

[FDibbins]

Hi FDibbins. Are you saying you can use vlookup directly in the conditional formatting window?

If that is so, Excel just got even better I can't wait to try it out

It will not return a value for you, if thats what you mean.

CF and DV work only on TRUE/FALSE (1/0) answers, so you only need to structure the rule formula to return those

[Tbez]

Thx FDibbins.

I tried to help, but ended up being the one learning new stuff

I hope you are getting this too Bow to The robots.

I attached an example with the vlookup in the conditional formatting rule.

Idk if I got this right, but I wrote the following formula

Please Login or Register  to view this content.

Worked like a charm.