Suggestions in Shortening a Formula

[TexasTrophy]

I have a csv file, that I get on a daily basis, that contains data for adding/updating products on my website.  The 2 particular columns of data that I am dealing with in this inquiry is the Product Number and Manufacturer.  The Manufacturer for some products is not consistent or nonexistent so I am trying to write a formula to make the Manufacturer consistent and complete across all products.

Example:

=IF(REPLACE(B2,4,99,"")="AAA","Alpha Co.","")&IF(REPLACE(B2,4,99,"")="BBB","Beta Co.","")

What this does is it checks the first 3 letters of the Product Number, which denotes the Manufacturer, then assigns the Manufacturer's name.  The issue is that there are over 600 Manufacturers which makes the formula extremely long and far exceeds the character limit.  Is there any other way aside from breaking down the formula into sizes within the limits?

[#2]

@TexasTrophy 

=IF(ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$20,MID(D2,4,3))),0)),REPLACE(D2,4,3,VLOOKUP(MID(D2,4,3),$A$2:$B$20,2,FALSE)),D2)

Is this example similar to what you want to do? The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or 2021. The ranges of the formula can be adapted.

[#3]

Thanks for the reply.

There are manufacturers listed in a column but they are inconsistent (Alpha, ALPHA, Alpha Co., etc) and/or missing so I need to define the manufacturer solely on the first 3 letters of the product number.

[#4]

@TexasTrophy 

=IFERROR(IF(ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$20,MID(D2,4,3))),0)),REPLACE(D2,4,3,VLOOKUP(MID(D2,4,3),$A$2:$B$20,2,FALSE)),D2),D2)

Does this return the expected results? I assume that the first 3 letters of the product number are a unique definition of the manufacturer of course. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or 2021. 

 

[#5]

@OliverScheurich 

I'm not sure I understand so let me show a short version of what I am dealing with. The first image is a portion of the actual data whereas I need to fill in the Manufacturing column with the correct name.  The second image is a list of proper names with corresponding 3 letters.  It's also important that it's the first 3 letters of the product number because others may have the same within the rest of it.

[#6 · Accepted solution]

@TexasTrophy 

=INDEX('proper names'!$B:$B,MATCH(LEFT('actual data'!B2,3),'proper names'!$A:$A,0))

For the actual layout of your data you can try INDEX and MATCH.

[#7]

@OliverScheurich 

I was trying to do it within the same file but that will work.

Thanks for your help!