Compare contents to list and change colour if present in list
Hello,
I'm looking for a formula that will change the colour of column B if I type in a code that exists in column A.
I found this on another similar thread asking about checking contents and returning a value (returning a value instead of changing the colour would be ok) but it didn't work when I tried it for mine-
=VLOOKUP(B2,$A$452:$F$5,2,FALSE)
Something to note is that your column-A values are text, not numbers. That may explain why you had problems with your vlookup() approach and is the reason for the "--" in the formula below.
Apply the following conditional format rule to the range $B$2:$B$500
Formula:
Please Login or Register to view this content.
Hopefully this works for you, let us know.
Last edited by GeoffW283; 11-08-2020 at 06:59 PM.
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
Hello,
Thanks for your response. That code works!!
If you have time I have some questions for you-
1. Why would the column-A values be text, instead of numbers?
2. Do you know if there is a way that this formula could work if I typed in a 3 digit code instead of a 4 digit code? As in the
codes do not need to be an exact match, rather the B column could be a shorter version of the A column (111 instead of
1112).
3. What is the '0' right before the closed brackets?
=ISNUMBER(MATCH(B2, --$A$1:$A$500, 0 ))
To answer your questions in order:
1. I've no idea! How did column-A get created? Perhaps there's a clue there. If you want them to be numbers rather than text there's various ways to do that. For example: select all of col-A then click on the yellow warning dropdown and select "Convert to number" or use ribbon > Data > TextToColumns.
2. Is it always the leftmost three digits in column-A that you want to compare with? If so the the following small change to the formula should work:
Formula:Please Login or Register to view this content.
3. The zero before the close brackets is just the third parameter to the match() function. Zero means that match() looks for an exact match. -1 or +1 do inexact matches - check out the help for the match() function for further details (however, with reference to (2) above I don't think this is the kind of inexact match you are looking for).
Let me know if you have additional questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks