Forum Discussion

Copper Contributor

Dec 21, 2022

Solved

Search Box Formula

Hi, Can anyone help me out with this very basic scenario involving a search box. I have added a snippet but cannot understand why its not filtering when trying to search "Advisor Name". ...

excel

Formulas and Functions

Patrick2788

Dec 21, 2022

mesa88

Try this:

=FILTER(Gradbay,ISNUMBER(SEARCH(H2,Gradbay[Advisor Name])),"No Record")

Detlef_Lewin

Silver Contributor

Dec 21, 2022

mesa88

Easy. There is no Advisor Name "saif".

mesa88

Copper Contributor

Dec 21, 2022

Thanks for responding. Do forgive me if I am missing something here- advisor named "saif" is in the table- the first name on the list.
I have tried changing the formula around to search for "gradbay" and it seems to work and search that column.

Patrick2788
Silver Contributor
Dec 21, 2022
mesa88
Try this:
=FILTER(Gradbay,ISNUMBER(SEARCH(H2,Gradbay[Advisor Name])),"No Record")
- mesa88
  Copper Contributor
  Dec 21, 2022
  Amazing Patrick. That worked perfectly.
  
  What is the significance of "ISNUMBER" in the Syntaxx
  
  Thanks Again
  - Patrick2788
    Silver Contributor
    Dec 21, 2022
    Although your data is partially redacted, my guess was your original FILTER did not pull the record because "Saif" was in the advisor column, but "Saif" wasn't the only thing in the column. If SEARCH is successful, it returns a number. When SEARCH fails, it returns #VALUE! error. Does the result of SEARCH produce a number? When it does, FILTER pulls the record.