Excel how to find values in 1 column exist in the range of values in another

Question

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details.

Something like this

A               B q123           as124 as124          gh456 ff45           q123 dd1             dd2 xx2 xx3 xx4 

and so on...

I want to find if the names in column A exists in column B or not.using excel

Answer 1

This is what you need:

 =NOT(ISERROR(MATCH(<cell in col A>,<column B>, 0)))  ## pseudo code 

For the first cell of A, this would be:

 =NOT(ISERROR(MATCH(A2,$B$2:$B$5, 0))) 

Enter formula (and drag down) as follows:

You will get:

Answer 2

Use the formula by tigeravatar:

=COUNTIF($B$2:$B$5,A2)>0 – tigeravatar Aug 28 '13 at 14:50

as conditional formatting. Highlight column A. Choose conditional formatting by forumula. Enter the formula (above) - this finds values in col B that are also in A. Choose a format (I like to use FILL and a bold color).

To find all of those values, highlight col A. Data > Filter and choose Filter by color.