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.
HansVogelaar 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?
I'd use a range like this:
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!!