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.
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?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.
HansVogelaar Thanks for the quick answer. I see your point. Just to bad for me 🙂
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)
