SOLVED

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

Copper Contributor

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 saying if the cell with the part number contains in any position one of the following letters please return the value assigned to this letter: 

 

In other words: if the part number contains "TR" please return "small", if it contains "PN" please return big etc. 

 

TRsmall
PNbig
CWRmiddle
EXTlarge

 

I tried several options but none of them was working. 

 

Thanks in advance for your help

 

19 Replies

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 

Enter the little list from your post into a worksheet - it doesn't matter where.

Select its first column, with the letter combinations.

Click in the name box on the left hand side of the formula bar.

Type a name, for example Parts, and press Enter.

Select its second column, with the sizes.

Click in the name box on the left hand side of the formula bar.

Type a name, for example Sizes, and press Enter.

 

Now let's say your part numbers are in E2 and down.

In the cell next to it, enter the formula

 

=INDEX(Sizes,MATCH(TRUE,ISNUMBER(SEARCH(Parts,E2)),0))

 

or

 

=TEXTJOIN("",TRUE,IF(ISNUMBER(SEARCH(Parts,E2)),Sizes,""))

 

If you don't have Excel in Office 2021 or Microsoft 365, confirm with Ctrl+Shift+Enter.

Then fill down.

@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

Thanks for your quick answer - I have tried both of your options and in the first one I get N/A as result and in the second one #NAME? error. Any idea? Thanks
best response confirmed by martin7b (Copper Contributor)
Solution

@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.

Thanks a lot! With your help I was finally able to built one on my sheet! I really appreciate your help. Have a great day.

This looks like something i have been strugeling with ... However. Is it posible to the two sheets as two different files insted of inside the same worksheet?@Hans Vogelaar 

@gylle1337 

It's possible but I would not recommend it. The workbook with the lookup list would have to remain open, otherwise the formulas would return an error. That kind of defeats the purpose.

@Hans Vogelaar Thanks for the quick answer. I see your point. Just to bad for me :) 

@Hans Vogelaar Don't know if i can post this here or i need a new post enterly?

 

However the solution given in this threat is very close to what im looking for. Dont know if it is posible tho. To have an open sheet search for words for a not open file.

 

my problem is that i have many sheets that need to search for the same values and i dont want to update the database across all of the sheets. So if the database/list chould be located only one place then i chould updatse it just once.

 

excampels in photos:

 

Skærmbillede 2022-04-22 222959.pngSkærmbillede 2022-04-22 222942.png

@gylle1337 

That is a rather awkward setup. Moreover, you're going to run into problems since "roastbeef" contains "roast". "roast potatoes" would classify as both pork and beef...

ok thanks :)
i do think i can you code to work for tho :) is there a way to have value return blank ( " " ) if the word is not found or if the cell that is being searched is blank.
Thats ok ... i thought i chould make a copy from DB to active sheet with ='[DB.xlsx]Ark1'!$A$1:$E$18

but it just returns #Value!

Well thanks for your time
i'll keep looking

@gylle1337 

You can replace

 

=some_formula

 

with

 

=IF(cell="", "", IFERROR(some_formula, ""))

@Hans Vogelaar I have a similar problem as described in this thread. I have a table titled "Properties" with a column titled "Property Name" listing properties by name and another column titled "County" listing which county each property is found within. Each property is found within one of 4 regions. I want to use the associated county to prescribe a region to each property in a third column titled "Region". I have a separate table titled "Counties by Region" with each region number as headers and the counties contained within in their respective region column. How do I write a formula in the "region" column of the "properties" table that searches for each county within the "Counties by Region" table and returns the column header in which the county name was found?

@goldmax 

I'd use a range like this:

HansVogelaar_0-1699542643737.png

You can then use a simple VLOOKUP or XLOOKUP formula to return the region that a specific county belongs to.

Yep that's what I needed. Just had to look at the data differently. Thank you!!

@Hans Vogelaar 

Hello,

I realize that this thread is older but this works really well for my purposes with a few issues. 

 

Can you give a breakdown of the formula in Name Manager?

=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A)-1,1)

 

I'm using this on part numbers instead and have instances with similar part numbers returning the wrong data. I've outlined a few instances below.

 

1BerryBites
62CitrusZing
100MangoMunch
5PineapplePops
620KiwiSqueeze
7MelonMorsels

 

So 100, is returning BerryBites instead of MangoMunch.

 

Can you lend some aid?

 

Thank you.

@SS_SMP 

The formula says the following:

  • Start in cell A1 of the Lists sheet.
  • Go 1 cell down (because I assumed that A1 contains a header/column name), and 0 columns to the right, i.e. to A2.
  • Expand to 1 row less than the number of filled rows in column A. The -1 is to omit the header in A1 from the count.

If you do not have a header in A1, the formula should be

 

=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

1 best response

Accepted Solutions
best response confirmed by martin7b (Copper Contributor)
Solution

@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.

View solution in original post