Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from Dec 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

1 Answer 1

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


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.

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.