Conditional Format alternative

johnmpc

Board Regular
Joined
Oct 19, 2020
Messages
108
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?
 
yes, as you can see the sample data from post #8 above, only the highest cost with same code are high lighted
I don't want it to be highlighted. I want it to display the highest price.

If you look at my sample data here you'll see 2001ORGW i want it to return £8.53 in the product cost box when looking up a 2001ORGW

Is that possible?

Excel.jpg
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
one thing you can do is to set the font colour as the background like this

Book1
ABCDE
1CodeCostCodeCost
2Code11Code11
3Code12Code12
4Code13Code13
5Code14Code14
6Code25Code25
7Code26Code26
8Code17Code17
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=E2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B8Expression=MAXIFS($B$2:$B$8,$A$2:$A$8,A2)<>B2textNO

I don't want it to be highlighted. I want it to display the highest price.

If you look at my sample data here you'll see 2001ORGW i want it to return £8.53 in the product cost box when looking up a 2001ORGW

Is that possible?

View attachment 24473
 
Upvote 0
Thanks but Still not really there.
I'm creating it as a quoting tool. The product code they are selling is typed in, and it shows the price. But when there are duplicate codes it needs to show the highest price.
 
Upvote 0
Thanks but Still not really there.
I'm creating it as a quoting tool. The product code they are selling is typed in, and it shows the price. But when there are duplicate codes it needs to show the highest price.
based on the sample data in post #12 above, code 1 has 5 different costs in Col E and the highest is the only one showed on Col B, isn't that what you're after?

If not, would you post a sample date and desired result here?
 
Upvote 0
2001ORGW£5.50
2001ORGW£6.51
2001ORGW£7.83
2001ORGW£6.12
2001ORGW£7.21
2001ORGW£8.53
2001W£1.93
2001W£2.12
2001W£2.41
2001W£2.20
2001W£2.42
2001W£2.75
2007W£3.30
2007W£3.65
2007W£3.99
2007W£4.40
2102W£2.69
2102W£3.84
2102W£3.45
2102W£4.60
2201W£2.87
2201W£3.57


Heres some data.

CodeSupplierProduct DescriptionColourProduct Cost
2102WBTCAmerican Apparel Womens Fine Jersey Tee
£2.69​

I want to type the code in in the code columm and when it has duplicates in the price i want it to show the highest cost in the product cost box.
 
Upvote 0
like this?

Book1
ABCDEF
12001ORGW£5.50
22001ORGW£6.51
32001ORGW£7.83
42001ORGW£6.12
52001ORGW£7.21
62001ORGW£8.53
72001W£1.93
82001W£2.12
92001W£2.41
102001W£2.20
112001W£2.42
122001W£2.75
132007W£3.30
142007W£3.65
152007W£3.99
162007W£4.40
172102W£2.69
182102W£3.84
192102W£3.45
202102W£4.60
212201W£2.87
222201W£3.57
23
24
25Heres some data.
26
27CodeSupplierProduct DescriptionColourProduct CostHighest
282102WBTCAmerican Apparel Womens Fine Jersey Tee£2.69£4.60
292001ORGW£6.12£8.53
302007W£3.30£4.40
Sheet1
Cell Formulas
RangeFormula
F28:F30F28=MAXIFS($B$1:$B$22,$A$1:$A$22,A28)
 
Upvote 0
Got so stuck on the Vlookup couldn't see this option. THanks again.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top