Forum Discussion

lgil32's avatar
lgil32
Copper Contributor
May 29, 2020

Filter function in 365

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, UTILITAR...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 29, 2020

lgil32 

It could be

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

 

lgil32's avatar
lgil32
Copper Contributor
May 29, 2020

ThanksSergeiBaklan 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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 29, 2020

    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 

Resources