# Cross-reference two columns with text and return common text in third column

#### Gain

##### New Member
Hi,

I have two columns with text in the cells on the same sheet (Columns A and B in my example below). Some words appear in both columns and I need to find out which words are common between them. I don't need to know how many times they appear in both columns. How can I do this in excel? If it is not possible to be done in excel, what other software can I use to return this information?

Here's an example of what I'd like to be done, with the common text to appear in Column C (or wherever, I just need to know what text is common):

Column A
create
color

Column B
table
pair

Column C

In reality, some of the columns I'd like to look through have 70,000+ rows.

Thank you

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
This should do the trick

Code:
``=IFERROR(INDEX(\$B\$1:\$B\$3,MATCH(A1,\$B\$1:\$B\$3,0)),"")``

Then copy the formula down for as many rows as you have information.

Here is a VBA solution as well.

Code:
``````Sub matches()
On Error GoTo n
Dim r1 As Range, r2 As Range, r3 As Range, cel As Range
Dim str As String
Set r1 = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Set r2 = Range(Range("b1"), Range("b" & Rows.Count).End(xlUp))
Dim iArray()
i = 1
For Each cel In r1
str = r2.Find(What:=cel, After:=r2.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
str = r2.Find(cel, r2.Cells(1, 1), xlValues, xlWhole)
If Not IsNull(str) Then
ReDim Preserve iArray(1 To i)
iArray(i) = str
i = i + 1
End If
n:
str = vbNullString
Next cel
Set r3 = Range(Range("C1"), Range("C" & WorksheetFunction.CountA(iArray())))
For i = 1 To WorksheetFunction.CountA(iArray())
r3.Cells(i, 1).Value = iArray(i)
Next i
End Sub``````

Anyone out there that sees a better way to do the VBA part, speak up. I'm sure there is a cleaner way to accomplish this task.

I have 92 rows in column A and 73821 rows in column B. What should the values be in formula?

This should do the trick

Code:
``=IFERROR(INDEX(\$B\$1:\$B\$3,MATCH(A1,\$B\$1:\$B\$3,0)),"")``

Then copy the formula down for as many rows as you have information.
I have 92 rows in column A and 73821 rows in column B, what should the values be exactly in the formula? Thank you for this, it really is very helpful

I know there is a better way to write that formula, but since you have such different sized columns I would use the VBA. But, if you want to use the formula, I would change it to

Code:
``=IFERROR(INDEX(\$A\$1:\$A\$92,MATCH(B1,\$A\$1:\$A\$92,0)),"")``

But if you want to use the VBA...

Hit Alt + F11
Alt + I, then M
Paste the code I posted earlier
Hit Alt + F8
Then run the macro

Both should work. Let me know if y have any issues.

I tried using the formula and nothing happens when I hit enter or click out of the cell.
I tried to run the VBA but the following error message appears:

"Run-time error '91':
Object variable or With block variable not set"
I have the option to "End", "Debug", or get "Help"

when I hit debug, the following line of code is highlighted:

str = r2.Find(What:=cel, After:=r2.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)

I don't know why your having those problems. See, the test data below. I used the above solutions on this data and both worked. The only difference between the two is that the VBA solution will put everything t the top of Column C, whereas the formula solution will list the matches next to the value in column B as below.

Excel Workbook
ABC
1ah*
2bri*
3cd*
4*e*
5*w*
6*s*
7*d*
8*e*
9*ri*
10*t*
11*f*
12*aa
13*bb
14*cc
Sheet1

I followed your instructions for the vba and I continue to get the error message. I'm not familiar with excel, is there a simple setting or something that may cause this?
For the formula, I have a sheet open with only two columns of information. I select cell c1 and paste the formula, hit enter and nothing happens. Again, is there something simple that I'm missing? Thank you for your help, I really do appreciate it

Replies
4
Views
105
Replies
6
Views
320
Replies
0
Views
93
Replies
1
Views
101
Replies
1
Views
93

1,203,047
Messages
6,053,197
Members
444,645
Latest member
mee siam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back