Lookup based on multiple dropdown selections

[cedrikfd]

Hello!

I tried to search the forums and couldn't find something similar to what I'm looking for. Disclaimer: my searching skills aren't too good hehe

Hopefully this can be done relatively easily... I'm running a car dealership's inventory who sell for multiple brands and I want a very quick and easy way to lookup a MSRP on any given model.

I have a workbook with two worksheets: "Lookup" and "Data".

So far, in Data, I have: Maker, Model, Trim Level, Model Year, MSRP, Notes. I may possibly add more details, such as transmission, additional packages, etc, but for now I'm satisfied with the information available.

Obviously, the same maker may come up multiple times, same with multiple models (i.e. I have 15 Honda's, of which five of them are Civic Sedans, each with a different trim level). A specific maker, model and trim level may also be available in multiple model years (could have a 2014 and 2015 for instance).

In "Lookup", I would like to be able to select a Maker and that based on this selection, the Model drop-down only shows the selected maker's car. Once I select the model, I want the same thing to happen with the trim level, and then the model year. Once all of it is done, I want to be able to VLOOKUP the MSRP, Notes and whatever other field I may want to add later.

My problem is that I'm unsure how to, first, make a drop-down dependent on another and two, eliminate repeat entries (i.e. I've made the drop-down on makers but I have 15 times "Honda" appearing).

Where should I start?cars_inventory.xlsx

[mcmahobt]

For the Car maker model, you wouldn't really need to list the duplicated. You could make a named range that involves only one of each type of car. From there, the model, trim, and model year can be created using dependent named ranges.

[mcmahobt]

I was curious myself about some aspects of this project, but believe I got it working. Take a look.

[AlKey]

Try this array formula in B8

=INDEX(Data!E2:E55,SMALL(IF((Data!A2:A55=B3)*(Data!B2:B55=B4)*(Data!C2:C55=B5)*(Data!D2:D55=B6),ROW(Data!A2:D55)-1),1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.