May 29 2020 09:29 AM
In Excel (Office 365) I wonder how to use FILTER function to return not exact match. I mean if we filter from a cell (J2) with the word "UTI" how filter function could returns UTILITY, UTIL, UTILITARY and so on. Thanks for your attention =FILTER(A:G;(E:E=J2)*(D:D>0);"")
May 29 2020 09:42 AM
May 29 2020 10:25 AM
Thanks@Sergei Baklan for your reply. I tested your suggestion but it did not work. ISNUMBER function return nothing as the filter value is text; ISTEXT function returns many information but not as desired.
Returned value should contain all letters that includes filtered word; please take a look to the following picture
May 29 2020 11:15 AM
what formula do:
first we SEARCH J2 text value in column E. If found, SEARCH returns position number, otherwise error.
next, we wrap SEARCH by ISNUMBER. If J2 found and position is returned, ISNUMBER returns TRUE otherwise FALSE