Excel - hierarchical lookup columns

What are the best approaches to accomplish a hierarchical lookup in excel.
What I mean is, first find the matching main category and then based on that find all subcategory matches that belong to the main category.

So for example, suppose I have a some rows of text in Worksheet1 Col A each of which has embedded within the text a brand name and a product name. e.g. "Dell laptop computers are great" or " HP laserjet printers print fast". Dell and HP would be the main categories and computer and printer would be subcategories.

And suppose I have some lookup lists in another worksheet, Worksheet2.
Worksheet2 Col A holds brand names (unique list no repeating values e.g. Dell, HP, Apple). Worksheet2 Cols B and C hold product category in Col B and product name in Col C - brand names would be a repeating field, product name would be unique and the combination of the two would be unique and tie a product to a brand) e.g. Dell Laptop, Dell Desktop, Dell Tablet, HP Laserjet, HP Inkjet, HP Thermal

Back on Worksheet1, I want to search Worksheet 1 Col A for the occurrence of a matching product category from Worksheet2 Col A and display that in Worksheet1 Col B. So if the text says "Dell computers" then Col B would say Dell.

Then in Worksheet1 Col C I want to display a dropdown list of all matching products belonging to the product category in Worksheet1 Col B (the result of the search) so that the correct product can be manually selected from the drop down. So if the text says "Dell laptop computers are great" then Col B would say Dell and the dropdown in Col C would show all the products for Dell (e.g. Desktop, Laptop, Tablet, etc.) and the user will select the correct product.

Which formulas should be used where e.g. search, index, Vlookup, etc.

I have attached a file set up with the columns described above.

Thanks in advance.
excel-hierarchical-lookup.xlsx
LVL 1
bobinorlandoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
You can achieve this with:
1) a formula to determine the key word (brand) in the phrase, and
2) a named range that uses an OFFSET function to return the valid product types based on the brand found (if any)

I've modified your workbook by removing the descriptor rows, so now, on Sheet1, the header row is
A1: Text
B1: Brand
C1: Product

I've then taken your two data lists on Sheet2 and assigned them to two Excel Tables:  "Brands" (col A) and "BrProducts" (col B:C).  This will be helpful in keeping your functions dynamic if you add/remove brands or products in the future.

1) In cell B2, you insert the following array formula (entered with [Ctrl]+[Shift]+[Enter]) to display a valid brand name that may appear in the text in A2:
=IF(SUMPRODUCT(--(IFERROR(FIND(Brands,A2),0)),ROW(Brands)-1)=0,"",INDEX(Brands,SUMPRODUCT(--(IFERROR(FIND(Brands,A2),0)),ROW(Brands)-1)))

2) Using the Name Manager, I created a named range called "PrLookup" that has the following formula in the "Refers to:" field:
=OFFSET(Sheet2!$C$1,IFERROR(MATCH(Sheet1!$B2,Sheet2!$B$2:$B$11,0),0),0,COUNTIF(Sheet2!$B$2:$B$11,Sheet1!$B2),1)
This determines the range on Sheet2 in the BrProducts table for any valid products for the displayed brand in column B
Named Range used for data validationThen, in cell C2, I added a Data Validation rule that references PrLookup for the valid list of values and copied that cell down.
data validation set up
I also added some other test brands and text to test.  I initially questioned why - if the text statement may actually describe a product type - you would ask a user to select the product.  Then I realized that this may be a teaching exercise to instruct persons on interpreting sentence structure.  Is that correct?

Regards,
-Glenn
EE-excel-hierarchical-lookup.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobinorlandoAuthor Commented:
Thanks Glenn. Actually no, the instant file is merely a template. Other data will be substituted for the simple examples I provided and because the number of brands and products will be quite long - thousands of products of which 40-50 may roll up to a single brand -- and the text is much more complex that we are searching so we're going to manually match the names rather than trying to match them programmatically. Code works great when matching a brand name, but the text has many inconsistencies in the way product names are stated and will produce too many inaccurate matches so for that task a person is more accurate, thus the drop downs to at least narrow down what they have to review but enable them to easily pick off the right match and keep moving.
0
Glenn RayExcel VBA DeveloperCommented:
Okay; interesting to hear more about how this will be applied.  Go ahead and experiment by expanding the existing tables and adding some sample text on the first sheet that more-closely resembles your actual data.  Let me know how it works.

Regards,
-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.