if your value is in column A & B starting from Row 2 then try this formula by writing in B2

=MATCH("*"&LEFT(A2,5)&"*",B2:B29,0) now drag it down ...

It will pick first 5 char from cell A2 then compare to column B and if found in column B anywhere then will return its number which you can be placed with if condition ......

=MATCH("*"&LEFT(A2,5)&"*",B2:B29,0) now drag it down ...

It will pick first 5 char from cell A2 then compare to column B and if found in column B anywhere then will return its number which you can be placed with if condition ......

Now, I've realized, on some occasions, on either list, the middle initial is included. Of course, these matches are overlooked by the exact match formula. So, I am making a partial match formula, but it won't quite work, yet. Here's what I'm trying.

=IF(ISNUMBER(SEARCH(B2,$A$2:$A$2651)),"PARTIAL MATCH","")

Any solutions would be much appreciated.

Mike,

I'm trying to do something similar.....match Legal company names with company names used by the GL; Often one will say "Corp." and the other will say "Corporation," so my v-lookup comes back #N/A. When you used the match formula, a number is returned.....what does that number represent? I've tried to use the match formula suggested, and it returns a number, but I have no idea what the number represents!

Thanks,

Stacey

I'm trying to do something similar.....match Legal company names with company names used by the GL; Often one will say "Corp." and the other will say "Corporation," so my v-lookup comes back #N/A. When you used the match formula, a number is returned.....what does that number represent? I've tried to use the match formula suggested, and it returns a number, but I have no idea what the number represents!

Thanks,

Stacey

The number represents the cell the value is in, if the value is #n/a then it means that it has not been located in the list

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Misty- Sep 7, 2010 05:44PMASHOK CHOURE- Apr 19, 2011 09:45AMgood

thanks