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