Solve the Problem of the day?

[jamesmev]

I am using Index/Match to utilize a lookup of phone numbers.
HOWEVER - - - If there is a misspelling or an addition of Jr, Sr... etc.... It clearly does not work.

I am looking to use a wildcard function in my search but running into road blocks.

A = Names
B= Phone Numbers

Look up = C1
Results = C2..

I need the results to populate the closest possible name.

John King -
Jonny King
Jon King
Jon King Jr
john King Sr.
Etc....

 

[inactiveuserps07]

James, use the following formula in C2:

=IFERROR(INDEX(B:B,MATCH("*"&D1&"*",A:A,0))&"","NO MATCH")

Then use an asterisk where needed in C1. For instance, the following entry in C1 will find any of the above:

j*n*king

If you wanted to find the "Sr." (perhaps knowing you had a Jr. and a Sr. in your list):

j*n*king*sr

I've written the formula referencing the entirety of columns A and B; however, I'd recommend, for the sake of efficiency and speed, that you limit both to your actual maximum ranges, e.g.:

=IFERROR(INDEX(B2:B1000,MATCH("*"&D1&"*",A2:A1000,0))&"","NO MATCH")