We help IT Professionals succeed at work.
Get Started

Excel - hierarchical lookup columns

bobinorlando asked
Last Modified: 2016-11-23
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.
Watch Question
US Data Team Lead
Top Expert 2014
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE