excel question about finding a number in a series

[Capital]

If I have a column of values (1,2,3,4, and so on) and I have another column of values...Is there a formula I could use to see if a value in one column is represented in another column?

I hope I explained that well enough.

[Wolfpack]

Really quick would be to do a "COUNTIF" on the column being checked. If you have an index of values in A, for example, and then the values you have as data in B, then do in column C, "=COUNTIF(B$1:B$x,Ay)" where X is the last filled cell in column B and y is the row in column A representing the number you are looking for. If the result is at least one, then obviously there is at least one occurence of the number you're looking for.

[Capital]

I could see how that would work, but my problem is that both series use 5 digit alpha-numeric values (ABC12 for instance). So my first series A1-A200 contains about 200 values. The second series has about 20,000 values. The goal is to see if all, some, or any are contained in the 20,000 values without doing a find for each.

I was hoping to write a formula using $ and then copy them to the rest of the first series - comparing to the second.

[moriarty]

Originally Posted by Capital

I could see how that would work, but my problem is that both series use 5 digit alpha-numeric values (ABC12 for instance). So my first series A1-A200 contains about 200 values. The second series has about 20,000 values. The goal is to see if all, some, or any are contained in the 20,000 values without doing a find for each.

I was hoping to write a formula using $ and then copy them to the rest of the first series - comparing to the second.

You can use VLookup if you can put one table in ascending order. Basically if it returns a valid value, it exists in the reference table. If it doesn't exist you'll get a NA.

[Butter]

vlookup, for sure.