Our systems are set up so that they only have the store section, and a description of the part sold:
Garden supplies - spraying can
So it is missing a level that says whether the product is used for Watering, Pests, etc.
Column A and B are where I put the some of the part name (enough to determine what the product is used for) and based on that piece of text the group name to which it belongs. This list would be ~100 rows long and keep growing.
Example:
Column A – B
1. Hose – Watering
2. Spray – Watering
3. Bug – Pests
4. Mole – Pests
….
Then I have Columns E and F right now, and am trying to figure out the formula to add G
Column E – F - G
1. Garden supplies - spraying can - Watering
2. Garden supplies - green hose - Watering
3. Garden supplies - long Hose by Hosers - Watering
4. Garden supplies – bug repellent - Pests
5. Garden supplies – mole traps – Pests
6. Garden supplies – swing set – swing set
The formula in G1 should look at column A1 (Hose), and looks to see if that text (doesn’t matter if capitalized or not) is in F1 (spraying can).
Since it doesn’t find a match, then it looks to see if the text in A2 (Spray) is in F1 (spraying can). It is, so then it returns the text from on cell over in B2: “Watering”.
It would do this through all the rows in A, and if it gets to the end and doesn’t find a match, then it would just return the product description (like swing set, above).
I think I may need to use a VLOOKUP with a FIND or SEARCH, but not having any luck. Any advice is appreciated -
Bookmarks