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, UTILITARY and so on. Thanks for your attention   =FILTER(A:G;(E:E=J2)*(D:D>0);"")

excel

3 Replies

    • 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