Index Match with partial match

[lpernambuco]

I Have an extensive spreadsheet, with very long text cells:

Content Spreadsheet with cells like: (about 20.000 lines)

1. Complete/Long Manufacturer Name
2. Complete/Long Product Full Description
3. Lookup columns to be added:​

1. 
   [*=2]Part Number
   [*=2]Type of Product
   [*=2]Classification

Reference Spreadsheet with cells like:

1. Short Manufacturer Name
2. Short Product Part Number
3. Type of Product
4. Classification

I need to bring information from Reference Spreadsheet to the Content Spreadsheet:

1. Compare Manufacturer --> If part of the LONG MANUFACTURER NAME matches the SHORT MANUFACTURER NAME --> It looks up for the TYPE OF PRODUCT (I was trying to use INDEX Match)

2. Compare Long Product FULL DESCRIPTION with Short Product PART NUMBER--> If part of the long/full Product Description matches the Short PRODUCT PART NUMBER - than I have a Column PART NUMBER to fill with that PART NUMBER from REFERENCE Spreadsheet

So I'm trying to see if part of a LONG DESCRIPTIVE CELL STRING matches a Short Reference Cell, when It does, using INDEX x Match to bring more information into this extensive spreadsheet

Can someone help me? thanks

lucia

 

[sergioMabres]

Hi Lucia
What you are looking for is a reverse partial lookup, I think that can be achieved if and only if for every short name there is one and only one long name.

You have to create a Long Manufacturer Name column in the Reference Spreadsheet by the Short Manufacturer Name
Then look for the Long Manufacturer Name using the Short Manufacturer Name and wild chars using some thing like:

=INDEX($Your_Long_Manufacturer_Name_Range,MATCH("*"&Your_Short_Manufacturer_Name_Cell&"*",$Your_Long_Manufacturer_Name_Range,0))

Where you write this formula is our New Long Manufacturer Name Range in the Reference Spreadsheet

Now we can write a normal INDEX MATCH in the extensive spreadsheet to get any column from the Reference Spreadsheet, for instance the Short Name using some thing like this:

=IFERROR(INDEX($New_Long_Manufacturer_Name_Range,MATCH(Your_Long_Manufacturer_Name_Cell,$Your_Short_Manufacturer_Name_Range, 0)),"")

I hope this helps
Cheers
Sergio