SOLVED

Search Box Formula

Copper Contributor

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".

 

mesa88_0-1671657788053.png

 

any ideas please? sorry, completely new to Excel here.

7 Replies

@mesa88 

Easy. There is no Advisor Name "saif".

 

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.
best response confirmed by VI_Migration (Silver Contributor)
Solution

@mesa88 

Try this:

=FILTER(Gradbay,ISNUMBER(SEARCH(H2,Gradbay[Advisor Name])),"No Record")
Amazing Patrick. That worked perfectly.

What is the significance of "ISNUMBER" in the Syntaxx

Thanks Again
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.
Thank you, thats makes sense. If i understand you correctly, previously searching "advisor name" would not bring any details/errors-just stayed blank.
The only problem I am facing right now is if i leave the search box empty it shows all the data blow- any way to stop from all the data showing?

@mesa88 

=IF(ISBLANK(H2),"",FILTER(Gradbay,ISNUMBER(SEARCH(H2,Gradbay[Advisor Name])),"No Record"))
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@mesa88 

Try this:

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

View solution in original post