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:
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") |