Search then validate given values against given sheet.

Closed
Thomas - Sep 20, 2016 at 04:52 PM
 Blocked Profile - Sep 23, 2016 at 07:00 PM
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.
Related:

4 responses

Blocked Profile
Sep 21, 2016 at 04:31 PM
YOu have a two part question. first, lets get the list labeld right. You are looking for Vlookup.

Vlookup is as follows:
https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-us&rs=en-us&ad=us

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

Have fun!

Let us know if you get stuck!
0
doctacarta Posts 2 Registration date Friday September 23, 2016 Status Member Last seen September 23, 2016
Sep 23, 2016 at 12:10 PM


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.
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Sep 23, 2016 at 12:42 PM
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
0
Blocked Profile
Sep 23, 2016 at 07:00 PM
Thank you for the long answer!

You are awesome!
0
doctacarta Posts 2 Registration date Friday September 23, 2016 Status Member Last seen September 23, 2016
Sep 23, 2016 at 12:46 PM
Yes, same person. Will do.

Thanks!
0