how to search a cell that existing in another column

  • Thread starter Thread starter vivian
  • Start date Start date
V

vivian

I need to compare column B and column C. If the cell in
column B exist in Column C, hight light it. Else nothign
happened. There are 120 values in column B and 250 values
in column C. How can i do this? is there any function
that i can use?
 
Frank Kabel said:
Hi
try the following
- select your cells in column B (assumption: you start in row 1)
- goto 'Format - Conditional Format'
- enter the following formula in this dialog:
=COUNTIF($C$1:$C$300,B1)>0
- choose a format for these cells
Click to expand...
[...]

Just

=MATCH(B1,$C$1:$C$300)

would be faster though.
 
I meant to say:

=MATCH(B1,$C$1:$C$300,0)

Aladin Akyurek said:
Frank Kabel said:
Hi
try the following
- select your cells in column B (assumption: you start in row 1)
- goto 'Format - Conditional Format'
- enter the following formula in this dialog:
=COUNTIF($C$1:$C$300,B1)>0
- choose a format for these cells
Click to expand...
[...]

Just

=MATCH(B1,$C$1:$C$300)

would be faster though.
Click to expand...
 
Aladin Akyurek said:
Frank Kabel said:
Hi
try the following
- select your cells in column B (assumption: you start in row 1)
- goto 'Format - Conditional Format'
- enter the following formula in this dialog:
=COUNTIF($C$1:$C$300,B1)>0
- choose a format for these cells
Click to expand...
[...]

Just

=MATCH(B1,$C$1:$C$300)

would be faster though.
Click to expand...

Hi Aladin
always impressed about your knowledge about perfomance issues!
I thought COUNTIF would be faster. Have you some details about the
performance difference (or a link to a previous discussion)

Regards
Frank
 
Frank Kabel said:
Aladin Akyurek said:
Frank Kabel said:
Hi
try the following
- select your cells in column B (assumption: you start in row 1)
- goto 'Format - Conditional Format'
- enter the following formula in this dialog:
=COUNTIF($C$1:$C$300,B1)>0
- choose a format for these cells
Click to expand...
[...]

Just

=MATCH(B1,$C$1:$C$300)

would be faster though.
Click to expand...

Hi Aladin
always impressed about your knowledge about perfomance issues!
I thought COUNTIF would be faster.
Click to expand...

The formula should be:

=MATCH(B1,$C$1:$C$300,0)

CountIf needs to look at the whole range C1:C300 for every value. MATCH will
stop looking further after a success.

Since we are talking of using in conditional formatting, the CountIf formula
can be shortened to:

=COUNTIF($C$1:$C$300,B1)

The speed difference will still hold though, excepting perhaps no success
cases.
Have you some details about the
performance difference (or a link to a previous discussion)
Click to expand...
[...]

http://www.mrexcel.com/board2/viewtopic.php?t=40233
 
The formula should be:
=MATCH(B1,$C$1:$C$300,0)

CountIf needs to look at the whole range C1:C300 for every value. MATCH will
stop looking further after a success.

Since we are talking of using in conditional formatting, the CountIf formula
can be shortened to:

=COUNTIF($C$1:$C$300,B1)

The speed difference will still hold though, excepting perhaps no success
cases.

Have you some details about the
performance difference (or a link to a previous discussion)
Click to expand...
[...]

http://www.mrexcel.com/board2/viewtopic.php?t=40233
Click to expand...

Aladin
thanks for the information
Frank
 
Back
Top