Search : in
By :

Partial Cell Match of Company Names in Excel

Last answer on Aug 18, 2009 1:21:17 pm BST mike1439, on Jul 13, 2009 4:12:49 am BST 
 Report this message to moderators

Hello,

I was wondering if anyone knows how to match similar but not identical cells in excel without specifying the exact match string. For example what formula would match the first word in one cell with the first word in another cell, or say the first 5 characters.

For example, matching the cell "ABC Learning Centres" with "ABC Learning Cents. Ltd" which would be then dragged down for 4000+ companies.

I want it to return a confirmation saying "match" or "true" or something similar so that i know the two columns are reliably matched. I will subsequently use this to match two data bases so I want the match to be as reliable as possible.

Thanks very much for any assistance

Configuration: Windows Vista Internet Explorer 7.0

Best answers for « Partial Cell Match of Company Names in Excel » in :
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
EXCEL: The INTERSECTION function ShowEXCEL: The INTERSECTION function You have a table consisting of rows and columns. Want to know the value of the cell at the intersection of a particular row / column in your table Example: Your table: - starts C3 to F3 ......
Repainting a cell using excel VBA ShowRepainting a cell using excel VBA Issue Solution Note Issue I am writing a code in excel VBA to generate a report.I am totally new to VBA.Here's my code Private Sub CommandButton1_Click() Dim a1 As Integer Dim d1 As Integer Dim...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...

1

mubashir aziz, on Jul 13, 2009 6:17:20 am BST
  • +3

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





Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

Reply to mubashir aziz

2

mike1439, on Jul 13, 2009 8:36:17 am BST
  • +1

Thanks very much for your help that works great!!

Reply to mike1439

3

Stacey, on Aug 12, 2009 10:28:47 pm BST

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

Reply to Stacey

4

 learning2, on Aug 18, 2009 1:21:17 pm BST

I have two lists of personnel names, a master list in A2:A2651 and a growing list in B2:B?. I need to find names in the growing B list that are also in the master A list. Using "Help" I made an array formula for exact matches that works: {=IF(OR(EXACT(B2, $A$2:$A$2651)),"EXACT MATCH","")}

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.

Reply to learning2