Lookup - if not found, change color

[kenetyw]

I am trying to figure out how to write a formula that will take every value from one sheet and compare them to another. I want any values that aren't found on the second sheet to change background color so I know that the other sheet doesn't have them.

Anyone have any ideas? I'm sure this is super simple if you know how to do it.

Thanks in advance!

 

[lenze]

hi and welcome to the board!!
Say your Sheet1 values are in column "A". Select column "A" and assign it a Name. Insert>Name>Define(say myRange). Now on Sheet2, select the column with numbers and Choose Format>Conditional Formatting. Use Formula IS option

=ISERROR(MATCH(A1,myRange,0))*(A1<>"")

Select a background color.
lenze

 

[kenetyw]

Thank you for your quick response.

I'm using the new Office so things were a little different, but I got the formula in just fine.

However, all it does is highlight every item entry on sheet2. This could be because in sheet1 the items are just a list: 60001-DG, 6001-00,1189-00, etc. but on the second sheet the item numbers were divided, with the part before the hyphen in one column and the part after in another. I got around this by making a column that displayed the two together (=B255&"="&c255). Does the match formula compare the output of the cells or their actual contents?

 

[lenze]

MATCH compares the value of one cell to the values in a list or array and returns the position as a number of that item in the list. If a match is found, a number is returned. If no match, you get an error. That error is what the CF formula tests for.
lenze