Solve the Problem of the day?

jamesmev

Board Regular
Joined
Apr 9, 2015
Messages
153
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....
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
James, use the following formula in C2:

Code:
=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.:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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
Top