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