I have an Excel spreadsheet.
In Column A, I have data values in Rows 2 thru 35,526.
In Column B, I have data values in Rows 2 thru 36,246.
I'm interested in verifying if all of the data values in Column A, appear in Column B (and vice versa).
I'm confident this can be done via the VLookUp function, but I need a little help.

Example:

	Column A	Column B
Row 1	Data XYZ	Data YYY
Row 2	Data AAA	Data PPP
Row 3	Data ZZZ	Data QQQ
Row 4	Data XXX	Data ZZZ
Row 5	Data YYY	Data KKK
Row 6	Data BBB	Data JJJ
Row 7	Data CCC	Data BBB
Row 8	Data QQQ	Data AAA
Row 9	Data WWW	Data XXX

Essentially, I'd like to create a 3rd column (i.e., Column C) and 4th column (i.e., Column D); where... Column C uses a VLookUp of Column A to see if the data set/value in Column A appears in Column B. If It does, I would like for it to say "Yes" or simply copy the value and highlight it. If it doesn't, then I would like for it to say "No" or simply "N/A".

And then something similar for Column D... where Column D uses a VLookUp of Column B to see if the data set/value in Column B appears in Column A. If It does, I would like for it to say "Yes" or simply copy the value and highlight it. If it doesn't, then I would like for it to say "No" or simply "N/A".

Can anyone help me out with this? I know it's simple, but I'm having some trouble.

Thanks in advance!

	Column A	Column B	Column C	Column D
Row 1	Data XYZ	Data YYY	"No"	"Yes"
Row 2	Data AAA	Data PPP	"Yes	"No"
Row 3	Data ZZZ	Data QQQ	"Yes"	"Yes"
Row 4	Data XXX	Data ZZZ	"Yes"	"Yes"
Row 5	Data YYY	Data KKK	"Yes"	"No"
Row 6	Data BBB	Data JJJ	"Yes"	"No"
Row 7	Data CCC	Data BBB	"No"	"Yes"
Row 8	Data QQQ	Data AAA	"Yes"	"Yes"
Row 9	Data WWW	Data XXX	"No"	"Yes"

Control C+Control V column A to Column B. Or did I miss something. You want the same data to appear in both columns?

Or I'd use an IF function. =IF(B2=C2, "Yes", "No")

You can then filter to yes or no for the values you're looking for.

Commander of Awesome;1530694 wrote:Control C+Control V column A to Column B. Or did I miss something. You want the same data to appear in both columns?

Or I'd use an IF function. =IF(B2=C2, "Yes", "No")

You can then filter to yes or no for the values you're looking for.

Column A has 35,526 rows
Column B has 36,246 rows

That's a difference of 720 (i.e., 36,246-35,526). I'm trying to understand which cells in Column B are part of that "720" difference. However, I'm not guaranteed that every cell in Column A is necessarily in Column B. Thus, the real difference could be higher than "720."

Make sense?

I stumbled upon this little formula/equation --> =IF(COUNTIF($A$2:$A$35526,B2)=1,"Match","")

So far, I think that might be doing the trick, but I'm not sold on it just yet.

Insert a new column next to column A with "1" in every cell (down to row 35,526). Use the vlookup function in the column next to the old column B (now column c), =vlookup(C1,$A$1:$B$35526,2,0). If it comes back w/ a 1, it's in column A, if it comes back w/ a 0 it's not.

Or, really, you don't need to insert a new column at all.

In column C, just do =vlookup(B1,$A$1:$B$35526,2,0). If it returns anything, that value from column B can be found in column A. If it doesn't return anything, the value from column B is not in column A.