Forum Discussion
Return a value from a list depending if value in another cell contains this
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.
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.
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.
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.
1 BerryBites 62 CitrusZing 100 MangoMunch 5 PineapplePops 620 KiwiSqueeze 7 MelonMorsels So 100, is returning BerryBites instead of MangoMunch.
Can you lend some aid?
Thank you.
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)
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?HansVogelaar
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.
- 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.
