Array formula to filter a results set only matches first value in the array

Hi there,

I have about 70000 rows of data that I would like to filter based on a list of about 50 text strings held in a second sheet. I would like all occurrences of the 50 text strings to be filtered from the 70000 rows. I am using a "substring" search with an array formula and a true/false filter to show the matches.

The attached file Customer List Filtering example guru.xlsx is a stripped down example of what I am trying to achieve taken from this excellent thread on filtering large amounts of data. This uses a dynamic named range BadCust as a basis for the array of values on the Bad Customer sheet. The array formula used is {=ISNUMBER(SEARCH(BadCust, B2))} but this only seems to work against the 1st value ABC in the Bad Customers worksheet. EFG which is also in the BadCust list shows FALSE. I would expect both of these to be TRUE.

I've tried replacing the dynamic named range with a fixed range of cells but that still only produces a match with the 1st value in the second sheet.

Any ideas?

Thanks, Paul

Thank you for the welcome. There is some great stuff on here :-)

Your solution works tigeravatar - much appreciated. It solves my immediate problem thank you. Was the problem with the original solution the array formula?

Now I just need to find a way to make your suggestion run as fast as an array formula! It takes quite some time on my core 2 duo machine to process 70,000 records against the list of 50 or so filters!

Paul

Tried that and I could not get it to return results. Shouldn't the first two parameters be transposed? i.e.

Please Login or Register  to view this content.

In the real example I am working with I am searching for a substring from BadCust in B2. I'm not aware that MATCH can do that and perhaps that is why your most recent suggestion is not returning results?

Paul

Thanks and kudos to your greater excel knowledge Tigeravatar. Much appreciated.
Paul