# How to check if cell contains a value of another cell

Here is the scenario:

On column A each cell has random text within it (names, address, account numbers, etc). Column A extends down to over 40 thousand rows. On column B I have a list of (account) numbers. Column B extends down to about 5 thousand rows.

I need to filter column A to show only the rows that contain the account numbers that are part of column B.

Neither column A nor B are in any specific order.

I think he above scenario speaks for itself, but I can provide a quick example if needed (don't want to over complicate it).

-

## migrated from stackoverflow.comDec 3 '12 at 22:33

This question came from our site for professional and enthusiast programmers.

Check out Excels Conditional formatting feature. You can do a FIND formula there and only display the rows you suggest or just color them... – jtheman Dec 1 '12 at 23:54

Inelegant but effective: Insert a column between your A and B columns, with

``````=VLOOKUP(A1,C\$1:C5000,1,FALSE)
``````

at the top and autofilled down. Then filter all, with that column deselecting `#N/A`.

Note '5000' above should be last occupied row number of your existing Column B.

-
pnuts, the formula you provided works when A1 EQUALS one of the values in column C. But in my case, A1 CONTAINS one of those values. That's what's giving me a hard time. – Antonio Oliveira Dec 2 '12 at 19:18
@AntonioOliveira NOW I see you did try hard to make this clear in your question, but I still misunderstood! If you could add your comment to your question I will delete my answer but otherwise better leave my WRONG ANSWER, because a cell that equals a value can fairly be said to contain that value! – pnuts Dec 2 '12 at 20:01
Inserting a column between A and C with this array formula `=IF(ISNA(LOOKUP(2^15,FIND(C\$1:C\$5000,A1))),"N","Y")` copied down from the top may be a start - but it may produce false positives ("Y") as unable to distinguish between, for example, 123 and 1234 in Column A. – pnuts Dec 2 '12 at 20:49
That is brilliant! I understand your caveat, but your formula worked like a charm for what I need. Thank you! – Antonio Oliveira Dec 2 '12 at 22:27
@AntonioOliveira Caveat can be worked around. If say space delimited in ColumnA and you are prepared to add a space at the end of each cell of ColumnA (to fully delimit last value there) then this version may be better: `=IF(ISNA(LOOKUP(2^15,FIND(" "&C\$1:C\$5000&" ",A1))),"N","Y")` – pnuts Dec 2 '12 at 22:31