# Conditional Format alternative

#### johnmpc

##### New Member
Hi,

I know that Conditional formatting doesn't carry over when using a Vlookup.

I am using a Vlookup to search data on a different sheet and return the value from a different column in a different cell.
Product cost is the returned value.
Code is the user entry.
The formula in Product cost uses vlookup to search a different sheet with a list of 3500 items for the text in the Code column. then returns the value in the Product cost column.

Some of the returned values are formatted on the basis that the original search text is duplicated.

I want to high light the returned value if the original text is duplicated.

Any thoughts?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### AlanY

##### Well-known Member
something like this?

Book1
ABCDEF
1CodeCostCodeCost
2Code11Code11
3Code22Code22
4Code33Code33
5Code44Code44
6Code52Code52
7Code66Code66
8Code77Code77
9Code84Code84
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=VLOOKUP(A2,E:F,2,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B9Expression=COUNTIF(F:F,B2)>1textNO

#### johnmpc

##### New Member
something like this?

Book1
ABCDEF
1CodeCostCodeCost
2Code11Code11
3Code22Code22
4Code33Code33
5Code44Code44
6Code52Code52
7Code66Code66
8Code77Code77
9Code84Code84
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=VLOOKUP(A2,E:F,2,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B9Expression=COUNTIF(F:F,B2)>1textNO
Kind of,

Its the Code that is duplicated not the cost
Heres the source. column A is the 'CODE'. i want to highlight the price being returned if it the code is duplicated.
Basically Different colours and sizes cost a different amount on some products so want to alert the user to check the price.

Also is there a way to get excel to return the highest price on the vlookup when there is more than 1?.

#### AlanY

##### Well-known Member
similar, really

Book1
AB
1CodeCost
2Code11
3Code22
4Code33
5Code11
6Code11
7Code66
8Code77
9Code33
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=VLOOKUP(A2,E:F,2,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B9Expression=COUNTIF(\$A\$2:\$A\$9,A2)>1textNO

#### johnmpc

##### New Member

Awesome!!! So simple .

Thank you

you're welcome

#### johnmpc

##### New Member

you're welcome
While i've got you. is there a way when there are Duplicate 'CODE' to return the highest Price associated to the 'CODE'?

#### AlanY

##### Well-known Member
try this

Book1
AB
1CodeCost
2Code11
3Code22
4Code33
5Code11
6Code13
7Code25
8Code23
Sheet1
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,E:F,2,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B8Expression=COUNTIF(\$A\$2:\$A\$8,A2)>1textNO
A2:A8Expression=MAXIFS(\$B\$2:\$B\$8,\$A\$2:\$A\$8,A2)=B2textNO

#### johnmpc

##### New Member
Will this return the Highest price from the product cost column?

#### AlanY

##### Well-known Member
yes, as you can see the sample data from post #8 above, only the highest cost with same code are high lighted

Replies
5
Views
30
Replies
3
Views
46
Replies
1
Views
35
Replies
1
Views
76
Replies
12
Views
180