Partial Search to Extract values in a Table (column A2:C142) into column K2:L24

I think your general strategy is going to be with helper columns that look something like:
1) Create a helper column with a reduced name that strips out "Corp" "Corporation" "Inc" "," etc to come up with only the unique parts of the string. Probably with nested SUBSTITUTE although you could probably do something clever with an array?
Well here's a guide for a UDF for exactly that, thinking about it getting text functions to play nice with arrays can be tough so this UDF might be your best bet.

2) Then run something to get a position for where that shows up
=SUMPRODUCT(ROW(range), --ISNUMBER(SEARCH(reduced_name, range))) looks like it would work.

3) You can feed that position into a straight INDEX(A1:A1000, position) to get the CIK and do the same thing on column B to get the ticker. You could wrap (2) into those, but I'd probably leave it in a helper column just so you're only doing the calc once.

Anyway that should about do it for you.

One thing that might help you is Microsoft's fuzzy lookup add-in. See
http://www.microsoft.com/en-us/downl....aspx?id=15011
I have not used it myself but others on this forum have had success.


Alternatively, here's a horrendous formula (although there is a great deal of repetition) that gets correctly 21 out of your 23 test cases.
It takes a somewhat different approach to Ben's strategy.

In J2 entered as an array formula with CTRL-SHIFT-ENTER:

Formula:

Please Login or Register  to view this content.

In K2 there is a virtually identical formula - the only difference being that it indexes into col-B instead of col-C.

The formula gets rid of "," (comma) characters which caused a good number of problems. It then (wildcard) compares the first word of the names and next it (wildcard) compares the first 5 letters of the text beyond the first word. The formula chooses the first instance where both tests match (if any) and failing that then the first instance where one of the tests matches. If there are no matches then the formula generates a meaningless match (as opposed to an error message).

Attached is an update to your workbook with the above changes. I have used CF in cols J:K to highlight the good matches (green) and the bad matches (pink).


Hope this helps

Please try at K2:L2

=INDEX(A$2:A$142,MATCH(LEFT(SUBSTITUTE($J2,",",),MATCH(0,INDEX(-MATCH(LEFT(SUBSTITUTE($J2,",",),ROW($C$1:INDEX($C:$C,LEN($J2))))&"*",$C$2:$C$142,),)))&"*",$C$2:$C$142,))

Thanks for the feedback and rep!