Filter Function to to return Data based on Partial text

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
145
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

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