43

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

Community
  • 1
  • 1
user1254579
  • 3,546
  • 17
  • 58
  • 95
  • 3
    Possible duplicate of [Find if value in column A contains value from column B?](https://stackoverflow.com/questions/11315551/find-if-value-in-column-a-contains-value-from-column-b) – Stevoisiak Oct 24 '17 at 14:54

3 Answers3

75

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:

enter image description here

You will get:

enter image description here

Mayou
  • 7,628
  • 15
  • 51
  • 91
  • 45
    Alternate: `=COUNTIF($B$2:$B$5,A2)>0` – tigeravatar Aug 28 '13 at 14:50
  • @tigeravatar I don't understand how just 'A2' says to "look in all of column A"? – David Doria Jun 26 '20 at 13:21
  • 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. – Jim50 Jan 22 '21 at 05:18
1

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.

sma
  • 11
  • 1
0

You can use Advance Filter, which is in the Data tab. I'm assuming here that you want to check which entries in column A are in column B (as your illustration). First, your data must have headers. Second step, create the criteria range, which is the entries in column A with the header of column B (your are telling excel to which column applies the criteria) In "List range" press arrow and select range B, including header. In criteria range, press arrow and select the range that you created in second step, including header. Press OK. What you see now are the entries which satisfies the criteria or entries in column A which are in column B. To show all the data again, go to Sort & Filter and press the Filter button. You also have the option of opening a second worksheet and extract the data to this sheet. Create the criteria in this sheet, which is the entries in column A with the header of column B as above . Start in this new sheet, select Advanced and choose "Copy to a second location". "List range" is the range of column B so that you must select the first worksheet to select range B. For the Criteria range, press the arrow and select the criteria range you have just created. For "Copy to" select a cell 2 rows below the bottom of the criteria range. Press OK. You will have an extraction of the entries in columns A which satisfies the criteria for column B, that is , appear there. Now, you can delete the rows of criteria range to bring the data to top of worksheet.

Jose Torres
  • 1
  • 1