Search then validate given values against given sheet.

Ask a question Thomas - Latest answer on Sep 23, 2016 07:00PM
I have two worksheets. One sheet is called "Valid" and contains values in the following cells:
A1= 1234
A2= 123
A3= 12345
A4=123456
The other sheet is called "List" and contains values in the following cells:
F1= 123
F2= 12345
I would like to create a formula that takes the values from sheet "Valid" and searches the entire worksheet "List", if it finds a value in "List" that exists in "Valid" then color that cell value in "Valid" green, for the values that don't exist then color those cells red.
Example above result would be:
In worksheet "Valid"
A1 = 1234 <- red
A2 = 123 <- green
A3 = 12345 <-green
A4 =123456 <- red
See more
+0
YOu have a two part question. first, lets get the list labeld right. You are looking for Vlookup.

Vlookup is as follows:

Then you want to do a conditional format against the product of the lookup:
http://ccm.net/forum/affich-910435-conditional-formating-formula-cell-text-change-color

Have fun!

Let us know if you get stuck!
+0

So in column C is where it is matching from worksheet "List" but ideally I want it to just use the native data from "Valid" A1:A4 and simply highlight green if there is a match from worksheet "List" and red if not. Do I need to use an IF statement instead? And like embed it into A1:A4?

+0
Thomas, Good afternoon.

??? Thomas and Doctacarta are the SAME person?

Scenario:

Sheet VALID
A1:A4 --> Numbers to be validated

Sheet LIST
F1:F2 --> The Number Valid LIST

Try to do:

Sheet LIST
Create a Named Range called TheList with F1:F2

Sheet VALID

Rule 1
Select A1:A4
Menu Conditional Format --> Using Formula

=ISNUMBER(MATCH(A1, TheList, 0))

Format Fill Colour as GREEN
OK

Rule 2
Select A1:A4
Menu Conditional Format --> Using Formula

=ISERROR(MATCH(A1, TheList, 0))

Format Fill Colour as RED
OK

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
ac3mark 5829Posts Monday June 3, 2013Registration date ModeratorStatus September 23, 2016 Last seen -
Thank you for the long answer!

You are awesome!