Filter function in 365

Highlighted
New Contributor

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

3 Replies
Highlighted

@lgil32 

It could be

=FILTER(A:G; ISNUMBER(SEARCH(J2;E:E))*(D:D>0) ;"")

 

Highlighted

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 pictureFilter Function.jpg

Highlighted

@lgil32 

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