Forum Discussion
Suggestions in Shortening a Formula
=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.
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.
=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.
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.
=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.
I was trying to do it within the same file but that will work.
Thanks for your help!