want to remove 'A' or 'The' from beginning or end of string in cell used for vlookup

[george2021]

In the two files that I am using for my vlookup, one file has 'A' or 'The' at the beginning of the string while the other has 'A' or 'The' at the end of the string - for example, 'A New Day' vs 'New Day, A' or 'The Great Lakes' vs 'Great Lakes, The'. How do I write my vlookup so that it trims 'A' or 'The' from the beginning or end of the string and searches on 'New Day' or 'Great Lakes'? Thanks!

 

[kevin9999]

You could simply use a wild card in your formula:

=VLOOKUP("*New Day*",table,column,False)

 

[Peter_SSs]

It is a little unclear just what you have, where, and which way you are doing the lookup, but see if something like this helps.

21 10 18.xlsm
	A	B	C	D	E
1	A New Day	2		The Great Lakes	5
2	Great Lakes, The	5		New Day, A	2
Lookup

Cell Formulas
Range	Formula
B1:B2	B1	=VLOOKUP("*"&MID(A1,2*(LEFT(A1,2)="A ")+1,FIND(",",A1&",")-1)&"*",D$1:E$2,2,0)