Forum Discussion

martin7b's avatar
martin7b
Copper Contributor
Dec 13, 2021
Solved

Return a value from a list depending if value in another cell contains this

Hi Excel community,   I am trying to build a large data file decoding part numbers. Part numbers have different lengths and compositions and they are all mixed up. All I need is a simple check sayi...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Dec 14, 2021

    martin7b 

    See the attached sample workbook. The Parts and Sizes names are dynamic in this version - they will automatically be adjusted when you add or remove part numbers. See Formulas > Name Manager for their definition.

tauqeeracma's avatar
tauqeeracma
Iron Contributor
Dec 13, 2021

Hi martin7b 

 

You may take help from SEARCH() functions, assume the parts numbers are stored in column A then use below formula column B.

 

 

=IF(IFERROR(SEARCH("TR",A3,1),0)>0,"Small",IF(IFERROR(SEARCH("PN",A3,1),0)>0,"Big",IF(IFERROR(SEARCH("CWR",A3,1),0)>0,"Middle","Large")))

 

 

You may also refer to the attached sample file for more understanding.

 

Thanks

Tauqeer

martin7b's avatar
martin7b
Copper Contributor
Dec 14, 2021

tauqeeracma 

 

Thanks for your quick answer - I have this solution - the issue is that the list with the codes is very long (+50 different codes and this makes the formula very long as well) - we are also modifying the list from time to time so I would like to have it dynamic for an enduser who is not familiar with formulas so he just can edit the list. sorry for the missing additional information from my side in the innitial post. Any idea? 

 

Thanks

Resources