A "like" condition for FILTER()

I have a search field and I like to allow the user to enter any part of a name they know and have that match the correct rows via a FILTER() condition.

Enter: Tom -> Result -> Tom Root, Tom Jones, Tommy Pinball
Enter: Root -> Result -> Tom Root
Enter: Tom Root -> Result -> Tom Root

What am I missing?

Thanks

Hi @TomRoot welcome to the forum and thank you for your question! The easiest way I could think of achieving this result is to use the Search feature of Lists:

This allows app users to search lists:

If I really needed to use filters, I would use the Search function from Honeycode Functions . For instance if I had a MyTable table that looked like:

Name
John Doe
Does This Work
John Work
Working Example

Then the formula =Filter(MyTable, "ISNUM(SEARCH( % , MyTable[Names]))", "Work") will return:

Name
Does This Work
John Work
Working Example

While the formula =Filter(MyTable, "ISNUM(SEARCH( % , MyTable[Names]))", "John") will return:

Name
John Doe
John Work

The second approach may be slower than the first approach so I'd be cautious using this with large tables.

Thanks!!

I understood the search feature of lists but the second part of your answer is what I was looking for.

I had toyed with using FIND in a similar way.

For what it's worth...what is the difference between SEARCH and FIND?

Thanks again for the reply.

Glad it helped! Find and Search are very similar, but Find is case sensitive, while Search is not, as the following examples illustrate:

Expression Result Interpretation
=search("nothing", "test") #VALUE! "nothing" is not not found
=search("test", "TestTest") 1 "test" is found at 1st position
=find("Test", "TestTest") 1 "Test" is found at 1st position
=Find("test", "TestTest") #VALUE! "test" is not not found (the text contains "Test" but not "test")