#VALUE Error

[Small Towne]

I'm trying to add a search box to a spreadsheet that contains a list of names and other data.
Here's the formula: =FILTER(H5:J11,ISNUMBER(SEARCH(C2,H5:J11)),"None")
I'm receiving the #VALUE error - A value in the formula is of the wrong data type.

Any help would be appreciated.
I've attached an example spreadsheet. Thanks.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Small Towne]

Thanks. I've attached a simple spreadsheet to show the error.

[AliGW]

What were you expecting it to return with nothing in the search box? What am I missing?

[AliGW]

Try this:

=FILTER(H5:J11,BYROW(H5:J11,LAMBDA(r,SUM(IFERROR(FIND(C2,r),""))))>0)

[Small Towne]

Hi AliGW:
It doesn't matter if I type something in the search box, I still see the #VALUE! error. Thanks.

[AliGW]

Did you see post #5?

[JohnTopley]

...................................

[windknife]

Another Way,

Please Login or Register  to view this content.

[AliGW]

From post #5:

=FILTER(H5:J11,BYROW(H5:J11,LAMBDA(r,SUM(IFERROR(FIND(C2,r),""))))>0)

[Small Towne]

That works! Thanks very much.