Filter Function to to return Data based on Partial text

[Ramadan]

I have this filter function to collect data based on the client name in Cell "B3" from all my sheets and it works good only if cell "B3" has the exact full name the same as intered in the sheet otherwise I get wrong result

this is my formula

=FILTER(District1!B:D,(District1!G:G=B3)+(District1!I:I=B3)+(District1!K:K=B3)+(District1!M:M=B3) and I repeat this function 8 times to get data accros my 8 sheets. so far it works fine

it simply check for the client name in 4 columns (G,I,K,M) in each sheet to give me the client information in (B:D)

But because names might be intered sometimes in a different way through our users, so I tried to edit the formula to collect any data with a patrial text either if first or family name was intered in B3...... NOTE: (first and family name are always both intered in one cell).. and I made it like this below

=FILTER(District1!B:D,(District1!G:G="*"&B3&"*")+(District1!I:I="*"&B3&"*")+(District1!K:K="*"&B3&"*")+(District1!M:M="*"&B3&"*"))

but it stoped giving any result while there is a true match

Can you please let me know how to fix this probelm and please put in mind that I will repeate this part of function 8 times to check 8 sheets

Thank you in advance

 

[hagia_sofia]

Hello, maybe something like:

=FILTER(District1!B:D,ISNUMBER(SEARCH(B3,District1!G:G)))

 

[Ramadan]

Hello, maybe something like:

=FILTER(District1!B:D,ISNUMBER(SEARCH(B3,District1!G:G)))

Good morning @hagia_sofia it works very good dear but how to add the other columns after G:G as you can see in my example I need to search in coulmns G,I,K,M

 

[hagia_sofia]

Hello again, like you did in the original formula. i.e.

=FILTER(District1!B:D,(ISNUMBER(SEARCH(B3,District1!G:G)))+(ISNUMBER(SEARCH(B3,District1!I:I)))+(ISNUMBER(SEARCH(B3,District1!K:K))))

and so on. But rather than selecting whole columns I would recommend you to limit the ranges so that only cells containing data are being calculated.

 

[Ramadan]

Hello again, like you did in the original formula. i.e.

=FILTER(District1!B:D,(ISNUMBER(SEARCH(B3,District1!G:G)))+(ISNUMBER(SEARCH(B3,District1!I:I)))+(ISNUMBER(SEARCH(B3,District1!K:K))))

and so on. But rather than selecting whole columns I would recommend you to limit the ranges so that only cells containing data are being calculated.

Looks very good however it's somehow long but it's ok for me, and I could repeat if for another sheet but I'm facing bad and stange issue, when I put a name in B3 that can be found in both sheets the formula returns only the results from sheet1 not all results in both sheets unless if the name is only found in sheet2 then it appear in the filter result.. bad luck I'm really frastrated all the time faceing new problem