Searching for Text string

[Vannah]

Hi everyone,

I'm a little new to this so please bear with me.

I have an Excel 2010 worksheet with 3 different tabs. One tab is where I have my master database and where all the other tabs get their information from. Tabs 2 and 3 are datasheets from different sources.

Excel 2010 32 bit

	A​	B​
1​	TAB 1 - MASTER DATABASE
2​	NAME	PROVIDER
3​	CAPTAIN AMERICA: CIVIL WAR	A
4​	ADJUSTMENT BUREAU, THE	B
5​	A PLACE IN THE SUN	C
6​	BOY NEXT DOOR, THE	B
7​	BOURNE SUPREMACY, THE	A
8​	AS ABOVE, SO BELOW	C
9​
10​	TAB 2 - Source X
11​	NAME	PROVIDER
12​	CAPTAIN AMERICA CIVIL WAR	=INDEX($B$3:$B$8,(MATCH(A12,$A$3:$A$8,0)))​
13​	THE ADJUSTMENT BUREAU	=INDEX($B$3:$B$8,(MATCH(A13,$A$3:$A$8,0)))​
14​	AS ABOVE SO BELOW	=INDEX($B$3:$B$8,(MATCH(A14,$A$3:$A$8,0)))​
15​
16​
17​	TAB 3 - Source Y
18​	NAME	PROVIDER
19​	THE BOURNE SUPREMACY	=INDEX($B$3:$B$8,(MATCH(A19,$A$3:$A$8,0)))​
20​	CAPTAIN AMERICA: CIVIL WAR	=INDEX($B$3:$B$8,(MATCH(A20,$A$3:$A$8,0)))
21​	A PLACE IN THE SUN	=INDEX($B$3:$B$8,(MATCH(A21,$A$3:$A$8,0)))

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

I would normally just use INDEX MATCH to find the corresponding provider to complete the missing info in the source tabs but my issue is that each source has a potentially different naming convention, which, when using a normal INDEX MATCH formula, will result in an error since the match is not exact.

So what I need is to somehow search not for the entire name but just part of it so I can establish which provider it belongs to. My biggest problems are with "The" and "A" being sometimes at the front or end of a name, commas and other punctuation etc. Can anyone help me do this, please?

 

[István Hirsch]

You should delete "A" and "The" from the start and the end of the string, as well as commas, colons etc. then make a comparison.

 

[nithin shettigar]

Paste in B12
=VLOOKUP(LEFT(SUBSTITUTE(A12,"THE ","",1),8)&"*",$A$3:$B$8,2,0)

 

[Vannah]

Paste in B12
=VLOOKUP(LEFT(SUBSTITUTE(A12,"THE ","",1),8)&"*",$A$3:$B$8,2,0)

Hi There,

Thanks so much for your reply. Can we change the Vlookup (won't work in my case due to left>right constraints) to make it work in an Index/Match scenario?

Thanks again.

 

[Vannah]

You should delete "A" and "The" from the start and the end of the string, as well as commas, colons etc. then make a comparison.

Hi,

Thanks for the idea but, unfortunately, this is not a viable option. Each sheet has an average of 7.000 lines worth of entries. To implement your idea would mean having to do this one by one (or run the risk or a formula getting it wrong) and this is just is not viable.

Thanks anyway, though.

 

[wangh3]

I think your best bet is to create a simple macro to delete out all the punctuations that could be an issue and then remove all spaces between text. Then you should be able to Index match appropriately.

 

[István Hirsch]

Hi,

Thanks for the idea but, unfortunately, this is not a viable option. Each sheet has an average of 7.000 lines worth of entries. To implement your idea would mean having to do this one by one (or run the risk or a formula getting it wrong) and this is just is not viable.

Thanks anyway, though.

Of course I do not mean converting the 7000 strings manually, but using a helper column to perform this with a formula, then selecting the correct name from the database.