I need help in populating cell drop down, and table value lookup.

Sample data in a worksheet. I have created a table and given it a name.

Sample Data (Table Name: Products):

Category    Product    Price
Fruit       Apple          1
Fruit       Orange         2
Drink       Coke           4
Drink       Pepsi          2

What I need to do:

In another sheet I need to have drop downs to choose Category & Product. The Price needs to be displayed in another cell when value has been chosen in both the fields.

| Category | Fruit   v |
| Product  | Orange  v |
| Price    |         2 |

Category, Product are drop downs that have unique values from the Products Table. They don't have to be cascading drop downs, but if that can be achieved it will be awesome. Price is a queried from the Products table depending on the values chosen in Category and Product drop down.


Your can use VLookup to find your price but you will need to create a calculated value to merge your category and product (put formula =B1 & C1 in cell A1 where column B contains your category and column C the product and copy the formula in all the cells in column A) since VLookup uses one column (left most column in range) only for lookup. You will also have to make sure that the link is in alphabetical order. Look here for info on VLookup: http://office.microsoft.com/en-ca/excel-help/vlookup-HP005209335.aspx

You will need your list of categories and products to be unique for your dropdowns so you will either create a separate list in another sheet or generate the list from your initial list of category/product/price. You will need this list numbered so add the formula A2=A1+1 or the equivalent beside your values. You can check this for the formula: http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

You can name your range to make your formulas easier to understand. It is explained in the previous link.

Finally, you will link each dropdown to your list. You will output the selected value in a cell you will reuse for your price formula. Name the cells CategoryId and ProductId. Note that the drop down will output the index of your selected value. This will be used to VLookup in your category list and product list.

Here is the formula for your price: = vlookup((vlookup(category,categoryId) & vlookup(product,productId)),pricelist,4)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.