Forum Discussion
EXCEL 365 Dynamic Array Search()
Hello everyone,
What does the formula calculate? Cell "$B$3 contains the substring "DE.9" which is searched for within the column of the table "tab_nessie_data[Project]". The opened search result is divided by the found results. Why do I get "#WERT!" as the result? .
Note: Using the formula by searching for numbers works great.
Thanks for your support
Use the third optional argument in FILTER (i.e. if_empty).
=IFERROR(FILTER(Table1[Project],SEARCH($B$4,Table1[Project],1)>=1),"")
No need for the IFERROR on the SEARCH part either.
9 Replies
Since you divide text on number, like ="text"/1
SergeiBaklan Thank's for the answer to my question.
Is there a solution to my challenge from your point of view. Importantly, the solution works in conjunction with the Filter()-/Array function. The data area is extremely extensive, so that only a solution with a very low run-time is practicable.
I'm looking forward to your proposal.
Many thanksPerhaps you can start by explaining what the challenge is. As SergeiBaklan explained, all your formula does is divide an array of texts by an array of numbers 1. Hence, it generates only VALUE errors. Obviously, that's not your goal. But what do you want to achieve?
