filter function (excel 365) with wildcard?

[daveyc18]

=FILTER('MTBRPT Clrng Accts'!A1:N100000,('MTBRPT Clrng Accts'!N1:N100000="092")*('MTBRPT Clrng Accts'!G1:G100000="*CLEAR*"))

trying to filter column N for "092" and pick up any rows that contain "clear" in column G

formula doesnt seem to work

 

[Alex Blakenburg]

Change it to
=FILTER('MTBRPT Clrng Accts'!A1:N100000,('MTBRPT Clrng Accts'!N1:N100000="092")*(isnumber(search("clear",'MTBRPT Clrng Accts'!G1:G100000))))

 

[Peter_SSs]

Nothing wrong with the formula suggested but if you wanted to shorten it a bit
=LET(r,'MTBRPT Clrng Accts'!A1:N100000,FILTER(r,(TAKE(r,,-1)="092")*ISNUMBER(SEARCH("clear",INDEX(r,0,7)))))

My main reason for posting though is that we don't know anything about the actual data in column G so just checking that it is the string "clear" that you are interested in and not the word "clear"

The suggested formulas would return rows with items in column G that included these words.
unclear
clearances
thermonuclear
denuclearised
etc

Just checking that is what you would want?