Forum Discussion

Heiko_Tiedemann's avatar
Heiko_Tiedemann
Copper Contributor
May 23, 2022
Solved

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...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 30, 2022

    Heiko_Tiedemann 

    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.

     

     

     

     

Heiko_Tiedemann's avatar
Heiko_Tiedemann
Copper Contributor
May 30, 2022

Riny_van_Eekelen 
Hi Riny,
This is a simple and great solution. (Please do not get this wrong)
I made some changes so that the function works on the filters () regardless of the length and position. 

=FILTER(Table1[Project];IFERROR(SEARCH($B$4;Table1[Project];1)>=1;"FALSE"))

 

Another question: Is there a solution to suppress "#CALC" ("CALC") as an erroneous result, such as "IFERROR".
THX

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 30, 2022

Heiko_Tiedemann 

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.

 

 

 

 

  • Heiko_Tiedemann's avatar
    Heiko_Tiedemann
    Copper Contributor
    May 30, 2022
    Hi Riny,
    I'm verry happy with your Support. Great Job 👍 See you soon

Resources