Only getting 211 results from a =FILTER - ISNUMBER - SEARCH formula

[Mrformulaheadache]

Good morning,

I'm using the formula =FILTER(A5:M520541,ISNUMBER(SEARCH(O1,G5:G520541)),"No Match") where O1 is a dynamic search box to search in column G of a large range of data in columns A to M.

The formula works fine apart from it only displays 211 results. Is there a way to show results for all rows? It is a large volume of data but I'm hoping there is a solution!

Using Windows and Excel 365

Many thanks

 

[jkpieterse]

Depends on your data. Does your data contain cells which are numbers? I bet your formula isn't performing very well with such a large range. Perhaps you should consider using Power Query to filter that table. I doubt if your data changes often enough to need a live updating formula.

 

[Mrformulaheadache]

Depends on your data. Does your data contain cells which are numbers? I bet your formula isn't performing very well with such a large range. Perhaps you should consider using Power Query to filter that table. I doubt if your data changes often enough to need a live updating formula.

Thank you for your reply.

There are numbers in other columns within the data collection A-M but not in the search field of Column G.
The large Range could definitely be the problem which is disappointing.
I'll check if i can get access to Power Query, not sure I can at present due to restrictions.

 

[jkpieterse]

I see you have Office 365, click the Data tab. Everything above Get & transform data is Power Query!