Search a range of text values for text within another range of text

[jhg05003]

Hello,

I am seeking to search one column of text 'School Name" for matches in another column from another table "Village Name". Often the school name contains the village name making matching easier. Some villages have numbers but I would be happy with a result that returns any match. My ultimate goal is match up the village code with the school code data set, but I could always do that though a separate step, as below. I am assuming VBA is the answer but not good enough to write code and not finding help in other posts.

PowerSearch.png

I realise I will still likely need to do a manual review to confirm some the matches and not all match up perfectly but this will be a huge help.

If you have any suggestions I would be hugely grateful.

Thanks,
Jonathan

[AliGW]

The sample workbook does NOT contain the same lookup list as shown in the screenshot.

Are you trying to get the village name AND number automatically, or is the village name to be entered manually?

A confusing sample spreadsheet with no manually entered results to show what you want is not much use - sorry!

Does thes HAVE to be done with VBA?

[kvsrinivasamurthy]

In C2

=IFERROR(VLOOKUP(LEFT([@[School name]],FIND(" ",[@[School name]])-1)&"*",Villages,1,0),"")

In D2

=IFERROR(VLOOKUP(LEFT([@[School name]],FIND(" ",[@[School name]])-1)&"*",Villages,2,0),"")

[jhg05003]

@kvsrinivasamurthy Wow thank you so much, that is great.

Thank you also @AliGW for taking a look. No I didn't need it to be done with VBA a formula is okay I just could not find any formulas that would return the matching cell contents only True/False.

I really appreciate both of you taking the time to assist.

Thank you.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.