I have a spreadsheet where I need all of Column B compared to Column C. If the variable in column B does not match anywhere in Column C I need that variable highlited red in Column B

Hi, Nice to know what you need.

Was there something you wanted to ask for.

Regards

I'm sorry I guess I need instructions on how exactly toget excel to perform that function. Is it a formula and if so how is it written?

Hi, Your description of the issue is not entirely clear.

Ifthis is your issue:

There is a series of values in cells in column C (for this example I put data into cells C2 to C14)

and you have a cell in column B containing a value (I used cell B2)

then you want cell B2 to be Red when the value in B2 matches one of the values in cells C2 to C14Do this:

Select cell B2

From the menu select Format - Conditional Formatting ...

In the 'Condition 1' drop-down select 'Formula Is'

In the formula box enter this:

=IF(ISNA(VLOOKUP(B2,$C$2:$C$14,1,FALSE)),FALSE,TRUE)

Click the 'Format...' button and select a red color in the 'Patterns' Tab

Click OK and OKNow when the value in B2 matches one of the values in C2 to C14 the cell color will be Red

The conditional formatting formula works like this:

VLOOKUP looks for the value in B2 in the range C2 to C14, as this is a single column range, and we don't need it to return anything else, the column offset is set to 1. FALSE in VLOOKUP ensures that it only returns a value if there is anexactmatch.

If it does not find an exact match it returns the NA error

The IF function tests VLOOKUP for the error value using ISNA

If VLOOKUP doesn't find a match it returns the NA error and ISNA is therefore TRUE

The structure of the IF function is IF(test, value if True, value if False)

In this case if the test is true i.e. VLOOKUP returned the NA error, IF returns FALSE

If a match was found, VLOOKUP does not return the NA error and the IF ISNA is false and IF returns TRUEThe conditional formatting is applied if the final outcome of the formula is TRUE, in this case when there is a match.

If you copy cell B2 to B3 then B3 will also have the same conditional formatting, but this time B3 is compared to the values in C2 to C14

If your requirements are different, please include all the details - please be specific about cells (

compared to Column Cmeans compared to cells C1 to C65536) and be specific about what is being compared to what (all of Column B compared to Column C does this mean B1 compared to C1, all the way down to B65536 compared to C65536)Regards

ok sorry for the lack of details. I currently have a series of values all in column B from rows 2 to row 800. Now in Column C I have nothing. What I would like to happen is when I scan an item into column C I need it to look thru all of column B and each row of column B to make sure that what I have scanned in matches something anywhere in column B, at the end when I am done scanning all my items into column C I need column B to highlight in red all the values that did not find a match in column C.. So I dont need a 1 to 1 comparison of say b2 to c2 I more so need it to compare both entire columns and make sure there is a match not just direct cell to direct cell. Hope this makes sense

Here is another formula to do the same thing, except using the =ISERROR & =MATCH() functions. =IF(ISERROR(MATCH(B1,$C$1:$C$14,0)),TRUE,FALSE)

MIKE

I currently have a series of values all in column B from rows 2 to row 800. Now in Column C I have nothing.With the above new info in mind, with my formula:

=IF(ISERROR(MATCH(B2,$C$2:$C$800,0)),TRUE,FALSE)

After you enter it into the Conditional Formatting,

ALLyour data in column B will be highlighted in RED, but, as you enter data into column C that matches any cell in column B the Red will disappear.

So you will be left with the RED cells in Column B only highlighting data that does not appear in Column C.MIKE

Hi, Use conditional formatting, as described before.

Select Cell C2 (assuming this is the first cell to have scanned data entered into it).

Use this formula in the conditional formatting 'Formula Is' box:

=IF(ISNA(VLOOKUP(C2,$B$2:$B$800,1,FALSE)),FALSE,TRUE)

Note the $ signs in the range B2 to B800, and no $ sign in the C2 address.

Change the format to Green and click OK, OK.Now select cell C2 and drag it down column C as far as you are likely to have scanned values entered.

As you enter values in column C the cell will be green if the scanned value matches something in column B.

Now in B1 use this formula for the conditional formatting and select a red color:

=IF(ISNA(VLOOKUP(B2,$C$2:$C$500,1,FALSE)),TRUE,FALSE)

Any value in column B that has not been entered in column C will be highlighted in Red.Change the column C range to match the number of rows you will use for entering your scanned data.

Regards

Omg Thank you so much both of the methods described above worked beautifully! Thank you to the both of you so much!

You're welcome. Thanks for letting us know it all worked

Regards

Humar

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History