Conditional Format alternative

[johnmpc]

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?

 

[AlanY]

something like this?

Book1
	A	B	C	D	E	F
1	Code	Cost			Code	Cost
2	Code1	1			Code1	1
3	Code2	2			Code2	2
4	Code3	3			Code3	3
5	Code4	4			Code4	4
6	Code5	2			Code5	2
7	Code6	6			Code6	6
8	Code7	7			Code7	7
9	Code8	4			Code8	4
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=VLOOKUP(A2,E:F,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=COUNTIF(F:F,B2)>1	text	NO

 

[johnmpc]

something like this?

Book1
	A	B	C	D	E	F
1	Code	Cost			Code	Cost
2	Code1	1			Code1	1
3	Code2	2			Code2	2
4	Code3	3			Code3	3
5	Code4	4			Code4	4
6	Code5	2			Code5	2
7	Code6	6			Code6	6
8	Code7	7			Code7	7
9	Code8	4			Code8	4
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=VLOOKUP(A2,E:F,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=COUNTIF(F:F,B2)>1	text	NO

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]

similar, really

Book1
	A	B
1	Code	Cost
2	Code1	1
3	Code2	2
4	Code3	3
5	Code1	1
6	Code1	1
7	Code6	6
8	Code7	7
9	Code3	3
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=VLOOKUP(A2,E:F,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=COUNTIF($A$2:$A$9,A2)>1	text	NO

 

[johnmpc]

Awesome!!! So simple .

Thank you

 

[AlanY]

you're welcome

 

[johnmpc]

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]

try this

Book1
	A	B
1	Code	Cost
2	Code1	1
3	Code2	2
4	Code3	3
5	Code1	1
6	Code1	3
7	Code2	5
8	Code2	3
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(A2,E:F,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=COUNTIF($A$2:$A$8,A2)>1	text	NO
A2:A8	Expression	=MAXIFS($B$2:$B$8,$A$2:$A$8,A2)=B2	text	NO

 

[johnmpc]

Will this return the Highest price from the product cost column?

 

[AlanY]

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