Excel Index and Match with contains

[JYonker]

Hello everyone,

I am looking to do an Index match function but want the formula to pull values that contain certain words in another cell.

This issue all stems from different naming conventions throughout the company (e.g. GE Capital is a subsidiary of General Electric). Basically I am trying to "Map" all of these to their entity names.

Example where it does not work:

I am wanting to map "GOLDMAN SACHS" to "GOLDMAN SACHS & CO". For some reason this returns a #N/A...

Example where it does:

"PPG INDUSTRIES INC" returns "PPG INDUSTRIES, INC."

Here is my formula:

=INDEX('All TRX Swen'!O:O,MATCH('12-29 Mapping'!C199,'All TRX Swen'!M:M,0))

Thank you very much for your help. I truly appreciate it!

Jasper

[outofthehat]

Are you absolutely certain that all your MATCH functions have the third parameter set to zero? An example spreadsheet would help us to investigate what's going wrong.

[JYonker]

Yes I am. Here is another example:

"PPG INDUSTRIES INC" is not found using index(match because in the array it is shown as "PPG INDUSTRIES INC." (With a period). Any help with this would be awesome.

Thx,
Jasper

[outofthehat]

Data cleanup of this sort is a classic problem, and the solution depends on how much the data changes each time (i.e. how many new names appear each time you go through the process.

Depending on the data and the business processes creating it, I would be inclined to do one or more of the following
1. Create a separate table listing all the variations of the different names (you can use Advanced Filter to extract unique values from your data) and create a master table that maps them to their 'correct' values using VLOOKUP. A bit of a pain to maintain if the list of variations changes regularly

2. Resign yourself to a bit of manual cleanup - filter the column to show only the #N/As and manually adjust to approved values. You probably end up having to do something like this to keep option 1 above up to date.

3. Preferred option if you're able: modify the place people are putting this data in. Use data validation on their workbook to ensure that they can only select from an 'approved' list of names. You could offer to set this up for them, and they might be open to it if you explain how much of your time they are wasting!