Pulling data from a list by matching partial criteria

Hi i'm new to this forum, so apologies if I post anything incorrect. I'm sure I'll learn fast.

I'm trying to retrieve data based on a string of letter in a name which match what I select in a cell coloured yellow.

Example, people enter names slightly differently and spelling mistakes can occur. Where a name is called "Steven Tyler", I want to find and retrieve all instances where my criteria in Cell "C2" is a partial name anywhere within the name field example:- "Tyle" or "even" or "teve". I can produce the retrieve to work on exact match, but i'm struggling do solve the partial.

Any help or advice would be so warmly received.

Spread sheet example attached

Jeff,

Thanks you for your warm welcome and reply.

Im not looking to match the full name as it does this already, however when spelling errors occur this makes me question the output data being correct. So I want to search in the yellow coloured cell and type eg. "teve" which would find all Stevens regardless of the surname and if Steven had been spelled incorrectly.

When you say VBA macros, I thought due to the complex nature this may be route to go down, but I know nothing about them yet.

You can a wild card to you search of column A, but "teve" could return any name with the four letters on it.

Please Login or Register  to view this content.

You can remove one of wild cards if that gives you a more accurate match.
Even VBA macro works similar to wild card, you can have either a whole match, or partial match, yes you can add case sensitivity to the match, but I doubt if VBA will find you your ideal solution.

Here is a solution using your file. This approach is a little different than what AB33 suggests although that approach is perfectly valid.

This approach makes three changes:

1. It adds an extra column on Data to indicate whether that name is a match for the name entered on Output. This formula uses the FIND function to see if the string appears in the name.
2. The Key formula references the value in this new column, rather than doing the test for a match itself.
3. The Count value on Output counts the number of hits shown in the new column on Data.

Note that the match is insensitive to case. So "Teve" will match "Ferd Teves" and also "Steven Tyler".

If you had given me a more complex rule, like "The string must match any name that contains that string but the match is allowed to be different by one letter" then we would be talking VBA.

Thank you both (AB33 & 6StringJazzer), they are just what I wanted.

im delighted

cheers

Hi

Thanks for sharing this vital information. I am working on a sheet where the names of the customers are not in proper order. To make it clear further the name David, Lee can be used Lee, David in the sheet. Could you please help me with this.

Regards,
Jagdev