# Thread: Help with dropdown calculating get from list.

1. ## Help with dropdown calculating get from list.

Hi Guys,

I am new to this forum and quite new to Excel and I would really appreciate your expert help. I have tried my best youtubeing and googlein but have come to a dead end since I dont even know what I am really looking for Tables, lookups, matches? Dunno anymore.

Scenario: I have a big-big stash of computer-parts and other electronics that I am going to sell. I have a price tag on everything in my excel list. When a buyer contacts me I want to use a conditional dropdown list to get to the pricetag without having to scroll my fingers off.

I have attached a best-I-can-do template in the thread. Basically I want to be able to get the price by choosing options in the dropdown menus.

What I would like Excel (and you of course) to help me with is: If I choose i.e "Nvidia" - "GPU" - "1 year" from dropdownmenus in sheet1, excel gets info "IF brand=Nvidia AND product type=GPU AND age=1 year;; THEN get price from sheet 3 list to new free cell.

Hope you have a slight clue about what I mean.

Thank you everybody!

2. Array entered , put this on sheet1

=INDEX('Table or list'!D2:D5,MATCH(1,('Table or list'!A2:A5=C6)*('Table or list'!C2:C5=E6),0))

3. You can save yourself a lot of bother updating the dependent data validation dropdowns by using a pivot table with slicers as in the attached on Sheet2 (and you won't need a sheet List).[There is nothing special or difficult about the pivot table, it takes less that 30 seconds to add - including the slicers.]
The slicers act like your dependent dropdowns, highlighting only available options and putting them at the top as you narrow down your search. What's more,

• if you don't select an age, you can see the prices of all the ages for a given product and/or brand.
• you don't have to select things in a particular order; your customer might be looking for a 1 year PC, select only those criteria from the right two slicers and you can give your customer a range of brands to choose from
• you can select multiple items within each slicer with the usual combinations of mouse-click and Shift or Ctrl on the keyboard

Not required, but I've made the Table or list data into a proper Excel Table so that you can add to it and the pivot table data source won't need to be updated manually - but the pivot would need to be refreshed (right-click on the pivot or slicer and choose Refresh).

Separately, and independently, in sheet1 I've implemented a version of Azumi's solution.

4. I finally got the match+index to work and now I can create it all without copying formulas, good experience!

Next project is using the pivot tables, seems much easier and the updating and adding possibilities are on a whole new level; no need to remake everything after adding a new product.

I am very grateful for your help Azumi and p45cal!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•