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 ......

Was this answer helpful?

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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

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

thanks