Report

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
Thanks for your time.
See more 
Helpful
+0
moins plus
YOu have a two part question. first, lets get the list labeld right. You are looking for Vlookup.

Vlookup is as follows:
https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

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!
Add comment
Helpful
+0
moins plus


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?

Thanks for your time.
Add comment
Helpful
+0
moins plus
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 - Sep 23, 2016 07:00PM
Thank you for the long answer!

You are awesome!
Reply
Add comment
Helpful
+0
moins plus
Yes, same person. Will do.

Thanks!
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!