Is there a search or filter function that works on partial results for strings?

Hi, I am a novice Excel user who's trying to learn by making a little database of some people in my ancestry.
I'm trying to implement a search function (search box) with which I can easily search for partial matches.
I have a list of different spellings of the same name, and I want to be able to find most of them by searching for only one of them.
Say we have the names Catherine, Catherin, Katrin, Kathrin, Katrine, and I type in Katrin, then I want all of the different spellings to be found through perhaps a percentage match.
Is there anything like this I can use in Excel?

Thank you for your reply!

I've already played around with filtering the results normally, and while it's good, it's not what I need.
My database is going to be pretty large, and I'd like to be able to sort out people with similar names like based on a percentage match of the search string.
I've looked at "Fuzzy lookup" and it seems like what I want. But I'd like it in the shape of a formula, appliable to a searchbox.

Hi, thanks for the response!

I fail to see how this is anything different to just using a filter function such as

Formula:

Please Login or Register  to view this content.

I'm looking for some formula/function that can find matches based on a portion of the search string. Like finding "Catherine" by typing "Katrin" for example, because they have 5 out of 6 letters in common.

to whom is your post #6 ?

Originally Posted by sandy666

to whom is your post #6 ?

I responded to you

so use @[username] eg. @sandy

so if you know what to use why asking about help?

Sorry Sandy, I'm not used to using forums haha.
Also I couldn't use the [at] symbol, because the site thinks that I am sending a "link, image or video" which I am not allowed to do until I've posted a few times :P

I know what I want, but I don't know how I can do it.
A simple filter function is not enough, as I've stated before.
I'll explain one more time

I want to have a function that when you input a full string into a textbox, for example "Katrin" it searches for:
A. Every person called Katrin
B. Every person with a name similar to Katrin with a different spelling, such as "Catherine, which has 5 out of 6 letters similar

The filter function only filters names that has the exact string "Katrin" within it. Such as "Katrine". But it will not find anything that is just a little bit different, such as "Catrin".
That's why I want to base the search upon results that have similarities with the inputted string, and not exact matches with the string.

I hope I made it clear. It is difficult for me to explain it

@ space username

Katrine <> Catrin
common characters , in this case, are atrin so you need for more Katrins use common characters as I did: at

here is another way via Power Query

Result

Catherine

Catherin

Katrin

Kathrin

Katrine

Katelyn Berry

Whoopi Fitzpatrick

Wilma Watkins

Ignacia Mathews

Matthew Fuentes

Please Login or Register  to view this content.

sandy666
Thanks again for the reply,
I can't use the [at] symbol as the site tells me I am not allowed to.

I think we are going in circles. If I have a database of potentially 10s of thousands of people, with 10 different spellings of catherin. Searching for only "At" will not help that much.
There will be so many people who will show up as a result that it would just be more efficient to search for each different spelling.
Also, to me it is very logical what you're saying. But I wont be the person who will use the search function. It will be my father who is too old to think about "common letters" in the names while typing his search string.

I gave you two solutions that work
If you are not happy - wait for someone else for solution you want

Have a nice day

Thank you for your response Glenn!
This does seem very intriguing. It seems to base the match % off of the amount of letters that the search string has in common with the names which is a great starting point!
However I don't really understand the formula, so I don't know how I would apply this to a FILTER function or a table, as in filtering out everything below say a 50% match.
It only seems applicable to a single cell at a time.
Or would you be so kind and explain it a little bit further?

I don't know what to make of it. I am able to filter them as you said.
But I can't apply this to a table, if I convert the names list to a table called "Table" and try to use the formula, it's not working.

=SUM(--(IFERROR(MATCH(MID(Table[Names];SEQUENCE(LEN(Table[Names]));1);MID($D$2;SEQUENCE(LEN($D$2));1);0);0)>0))/LEN($D$2)

I'd assume this is because of the SUM function summing it up into one cell, but I would like it to sum every row of the table without having to apply the function manually to every row.

I hope you understand my question. It's really hard to explain haha