vlookup or indexmatch on closest name match?

**jw01** · 05-14-2012, 11:14 AM

hello

having some trouble trying to do a vlookup or index match.

i have a list of ~2000 companies (worksheet Hoovers). Im trying to do a vlookup or match to try to see how many of our ppl are targeting those companies (from worksheet my List).

i tried the vlookup formula (column P in Hoovers sheet):
=VLOOKUP(B2,'My List'!$D$3:$E$1274,2,TRUE)
the above is finding the wrong match

can someone pls help? basically im trying to match the names of the companies and their may not be exact matches
for example:
in the "hoovers" worksheet this company is called: "Exxon Mobil Corporation"
in the "my list" worksheet the comany above goes by: "Exxon Mobil"

can someone pls help?

thx u *i have attached a sample*

**jw01** · 05-14-2012, 11:44 AM

any thoughts on this guys? thxs

**Cutter** · 05-14-2012, 11:56 AM

Maybe something like

=VLOOKUP(LEFT(B2,8)&"*",'My List'!$D$3:$E$1274,2,FALSE)

change the 8 to a cell holding that value so you can easily change it - or use a function to auto set it

**jw01** · 05-14-2012, 03:22 PM

hello

you mentioned to change left "8" to adjust to the name....how would i go about with that? thxs

**Cutter** · 05-14-2012, 03:38 PM

You could replace the 8 with a FIND() to return the position of a space, you could have a LEN() function multiplied by a factor (say .5) to check for half the string length.
eg: =VLOOKUP(LEFT(B2,INT(LEN(B2)*.5))&"*",'My List'!$D$3:$E$1274,2,FALSE) would search for "Exxon Mobil" instead of "Exxon Mobil Corporation"
Note: it just happened to work out perfectly for that example. It may not for others.