Filter Function to to return Data based on Partial text

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
151
Office Version
  1. 2021
Platform
  1. Windows
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
 
Hello, maybe something like:

Excel Formula:
=FILTER(District1!B:D,ISNUMBER(SEARCH(B3,District1!G:G)))
 
Upvote 0
Hello again, like you did in the original formula. i.e.

Excel Formula:
=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.
 
Upvote 0
Hello again, like you did in the original formula. i.e.

Excel Formula:
=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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top