Using Conditional Formatting to verify data

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
81
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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!
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!!!!!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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