Problem in checking two columns using (if) pls help.....:(

**ammy.rocks** · 05-04-2013, 10:42 AM

hi,
i have two discrete columns for e.g. A1 to A500 & B1 to B500 , now i want to check where A1 = b1,b2 b3 and to b500.
similarly a2 a3 .... so on till a500.

i tried using =IF(A1=(B1:B500),[TRUE],[FALSE])

BUT IN VAIN KINDLY GUIDE so that i can sort this one out

**FDibbins** · 05-04-2013, 10:50 AM

hi and welcome to the forum

try this, copied down...
=IF(A1=B1,TRUE,FALSE)

**daddylonglegs** · 05-04-2013, 11:06 AM

Perhaps try this formula in C1 copied down

=COUNTIF(B$1:B$500,A1)>0

**Tony Valko** · 05-04-2013, 11:12 AM

Maybe this...

=ISNUMBER(MATCH(A1,B$1:B$500,0))

Copy down as needed.

**ammy.rocks** · 05-06-2013, 02:12 AM

Originally Posted by Tony Valko

Maybe this...

=ISNUMBER(MATCH(A1,B$1:B$500,0))

Copy down as needed.

thanks for the help it works can we change the colour of true or false one ????

**wenqq3** · 05-06-2013, 02:20 AM

Originally Posted by ammy.rocks

thanks for the help it works can we change the colour of true or false one ????

Conditional Formatting

**Tony Valko** · 05-06-2013, 09:01 AM

Sure.

Let's assume the data is in the range C1:C500.

Select the *entire* range C1:C500 starting from cell C1.
Cell C1 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=C1=TRUE

Click the Format button
Select the desired style(s)

OK
OK

New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=C1=FALSE

Click the Format button
Select the desired style(s)
OK out