CODEWITHSUNDEEP
excelvba
Reynier
Reynier

Reputation: 2478

Compare each cell from column A with each cell from column B and colum C

I've a Excel file with three columns. Cells in each columns are different ranges for example column A has 797340 cells, column B has 91617 cells and column C has 95891 cells. I need to compare each value in column A and look if this value is on column B or it's in column C and if the condition is TRUE then bold the cell or change the color to red. If there any way to achieve this using Excel formulas? Any help?

Upvotes: 0

Views: 8426

Answers (3)

Santosh
Santosh

Reputation: 12353

You can use conditional formating. Kindly refer to below image. Its just a demo.

Select Column A> Goto Conditional Formatting >> New Rules >> Use Formula to determine which cells to format

Enter the formula in >> Format values where formula is true >> select the format >> OK

=OR( IF(ISNA(VLOOKUP(A1,B:B,2,0)),FALSE,TRUE),IF(ISNA(VLOOKUP(A1,C:C,3,0)),FALSE,TRUE))

enter image description here

Upvotes: 2

JAGAnalyst
JAGAnalyst

Reputation: 330

Unless there is a reason you need to handle this with code, you can set conditional formatting within excel based on a formula.

For example, you can create a new conditional formatting rule based on a formula such as:

=IF(ISERROR(VLOOKUP([Cell in Column A],[Column B Range],1,FALSE)),"FALSE","TRUE")

This formula will return true when a matching value is found in column B. Then simply apply the same rule again for column C.

Apply this rule to the entire range of your column A cells, and set the conditional formatting to return bold and red when true.

Good luck!

Example with ranges in one worksheet: Formula as applied is 

=IF(ISERROR(VLOOKUP($B3,$F:$F,1,FALSE)),"FALSE","TRUE")

Example

Upvotes: 0

Young Bob
Young Bob

Reputation: 743

To make life easy use conditional formatting and apply your own custom rule.

http://office.microsoft.com/en-gb/excel-help/quick-start-apply-conditional-formatting-HA010370614.aspx

Upvotes: 0

Related Questions