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

Solution 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:

Solution 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.

Comments

[Stevoisiak]

Possible duplicate of Find if value in column A contains value from column B?

[tigeravatar]

Alternate: =COUNTIF($B$2:$B$5,A2)>0

[David Doria]

@tigeravatar I don't understand how just 'A2' says to "look in all of column A"?

[Jim50]

David Doria, it won't by itself. It says take the value in A2 and see if it's in the range B2 -> B5. Then as you drag that formula down, the following cell will check A3 against B2 -> B5, and the next A4 and so on. Basically you take each item in A one by one and look for that in all of B.