How to check if cell contains a value of another cell

Question

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).

Solution

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.

Comments

[jtheman]

Check out Excels Conditional formatting feature. You can do a FIND formula there and only display the rows you suggest or just color them...

[Admin]

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.

[Admin]

That is brilliant! I understand your caveat, but your formula worked like a charm for what I need. Thank you!