Share via

how to determine if one value in a column exists in another

Anonymous
2012-06-27T02:27:42+00:00

Hi All,

I am currently working with two columns containing similar data, column A and B. Column B contains data with duplicates and contains thousands of values. Column A contains maybe 100 values.

What function should I use to determine if values in column A, exist in column B?

I've tried vlookup, and match functions and I believe I'm using them incorrectly, as my values return as N/A.

I would like the values to populate in another column and show values of "True" or "False".

Any help would be appreciated.

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-06-27T03:35:56+00:00

    Insert a  column between your columns A & B so that:

    Column A1:A100 have your data and Column C1:C1000 have duplicate data. 

    In B1 write the following and drag down till B100:

    =IF(ISERROR(VLOOKUP(A1,$C$1:$C$1000, 1, FALSE)),FALSE,TRUE )

    or you may use:

    =COUNTIF($C$1:$C$1000,A1)>0

    or something like:

    =NOT(ISERROR(MATCH(A1,$C$1:$C$1000,0)))

    The ranges may be modified according to the size of your data.

    Regards,

    Vinay

    100+ people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-06-28T02:24:18+00:00

    Two possibilities that immediately come to mind.

    a) Your data looks like numbers but is stored as numbers in one column and as text in the second column.

    b) Trailing spaces in the data. i.e. "CAT" is not the same as "CAT       ". Many a times, downloaded data or converted data has these empty spaces which may cause the VLOOKUP to return FALSE.

    Check these two. In case you find either of the two, manually change a couple of entries and see if the corrected data yields a TRUE.

    In case none of these is working, you may probably give us some sample data, or better still, upload your file on a File Sharing website and give the link here for us to check.

    Thanks

    Vinay

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-06-27T19:16:26+00:00

    Thanks Sir..

    Using all three formulas above, my values populate to false.

    However, I manually checked a couple values in column A to see if they exist in column C, and some of these values do exist.

    Is there a reason why the formulas are not picking up these values?

    I dragged the formula down the entire column  of B. Could this be a format issue?

    Thanks!

    1 person found this answer helpful.
    0 comments