Conditional Format alternative

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.
Excel.jpg

Any thoughts?
 

Some videos you may like

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
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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?.
Excel.jpg
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 
Solution

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Awesome!!! So simple .

Thank you
 

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Will this return the Highest price from the product cost column?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
yes, as you can see the sample data from post #8 above, only the highest cost with same code are high lighted
 

Watch MrExcel Video

Forum statistics

Threads
1,114,522
Messages
5,548,545
Members
410,848
Latest member
anuradhagrewal
Top