Dynamic search box issue

Question

Hi

I have a problem with the search box filter function results, when it's empty it shows all the data that I'm searching for but it is supposed to show NA instead.

https://we.tl/t-galkFpQDbU?utm_campaign=TRN_TDL_05&utm_source=sendgrid&utm_medium=email&trk=TRN_TDL_05

This is the Wetransfer download link.

How to fix that, please help.

Answer 1

It never occurred to me, but when you SEARCH for an empty string, all cells that contain something return the number 1. Only cells then are truly empty return #VALUE!.

That's why your FILTER function picks-up all rows with something in it, when you search for nothing in K17. Wrap the formula in

=IF(K17="","NA", FILTER( etc........ ))

Since your Excel version supports dynamic array functions, you may want to consider the following formula in I20, in stead:

=IF(K17="",NA(),FILTER(A20:E48,BYROW(SEARCH(K17,A20:E48),LAMBDA(arr,COUNT(arr)))))

Answer 2

Hello Riny, it worked like a charm...

=IF(K17="","NA", FILTER( etc........ ))

Much appreciate your help, thank you.

Answer 3

Hi I am Leon.

When searching for a blank or empty string, cells that contain any value will return 1 as a result, and completely empty cells will return the #VALUE! error, hence your problem. So Riny_van's formula should work perfectly :-)

Answer 4

Hello

I'm looking up to improve the search results by the following...

In the same Excel file for example C20 cell, I have linked it by file (picture) with a hyperlink but the search results appear without this link, only the text "Hinge".

I need the search result "Hinge" to appear with the hyperlink so I can click on it to open its picture.

Thanks

Answer 5

I believe that's not possible. Perhaps someone else can prove me wrong.