Using Conditional Formatting to verify data

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Hi All!

We track incoming construction contracts using excel, but lately there have been a lot of contracts with the wrong address on them.

I would like it so when we enter the owners name, excell will reference our master sheet and turn red if the address does not match.

So, I have a master like this:

Owner 1 : 123 Fake St.
Owner 2 : 456 Fake St


And if my staff enters Owner 1, with 456 Fake St. It will turn red, and let them know something's wrong.

I thought it was Vlookup in conditional formatting, but I can't get it. Maybe I'm going down the wrong path.

Thanks in advance!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
202
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
MrExcelPlayground2.xlsm
IJKLM
19Master List
20Owner 11 first streetOwner 1One first st
21Owner 22 second streetOwner 11 first street
22Owner 33 third streetOwner 22 second st
23Owner 44 fourth streetOwner 33 third street
24Owner 3three third street
25Owner 44 fourth street
26Owner 44 fourth st.
Sheet34
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:M26Expression=VLOOKUP(L20,$I$20:$J$23,2)<>M20textNO


But I might use a lookup function and forget about typing in the address.
 

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
MrExcelPlayground2.xlsm
IJKLM
19Master List
20Owner 11 first streetOwner 1One first st
21Owner 22 second streetOwner 11 first street
22Owner 33 third streetOwner 22 second st
23Owner 44 fourth streetOwner 33 third street
24Owner 3three third street
25Owner 44 fourth street
26Owner 44 fourth st.
Sheet34
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:M26Expression=VLOOKUP(L20,$I$20:$J$23,2)<>M20textNO


But I might use a lookup function and forget about typing in the address.
Thank you, this looks like it works on my sample sheet, but not in my actual spreadsheet.

I am going to try to trouble shoot for a bit and see if I can't figure it out. I may ask an additional question or 2! Let me see what I can do first!
 

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
MrExcelPlayground2.xlsm
IJKLM
19Master List
20Owner 11 first streetOwner 1One first st
21Owner 22 second streetOwner 11 first street
22Owner 33 third streetOwner 22 second st
23Owner 44 fourth streetOwner 33 third street
24Owner 3three third street
25Owner 44 fourth street
26Owner 44 fourth st.
Sheet34
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:M26Expression=VLOOKUP(L20,$I$20:$J$23,2)<>M20textNO


But I might use a lookup function and forget about typing in the address.


Hello, I have unexplainable results when I try to use this on my spreadsheet. It reacts very erratic. I can get it to work on a blank sheet every time, but not my spreadsheet.

After spending 8 hours, I can't even understand what it's doing. It picks and choses what to light up red.

Is there a different formula I could try ?
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
202
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

This is essentially the same thing.
MrExcelPlayground2.xlsm
IJKLM
19Master List
20Owner 11 first streetOwner 11 first street
21Owner 22 second streetOwner 12 first street
22Owner 33 third streetOwner 22 second st
23Owner 44 fourth streetOwner 33 third street
24Owner 3three third street
25Owner 44 fourth street
26Owner 44 fourth st.
Sheet34
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:M26Expression=INDEX($J$20:$J$23,MATCH(L20,$I$20:$I$23))<>M20textNO


If you had office 365 you could try using xlookup. I can't understand why it doesn't work.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this mod to James's original formula
Excel Formula:
=VLOOKUP(L20,$I$20:$J$23,2,0)<>M20
 

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this mod to James's original formula
Excel Formula:
=VLOOKUP(L20,$I$20:$J$23,2,0)<>M20

I added my settings, thank you.

FLuff, that fix worked! All it needed was the ,0

I can't even troubleshoot what was happening before, it was randomly coloring stuff. THANK YOU BOTH!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,551
Office Version
  1. 365
Platform
  1. Windows
I added my settings, thank you.
Thanks for that.
The probable reason you were getting values highlighted at random, is that the formula was doing an approximate match, rather than an exact match.
 

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Thanks for that.
The probable reason you were getting values highlighted at random, is that the formula was doing an approximate match, rather than an exact match.
Thank you for the explanation. I stared at my wall decor for hours. It reads "Everything is figureoutable" But I was stumped! :D
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

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
Top