Hi. I know how to play around with basics functions in excel, but there is one thing I'd like to do and cannot find how to do it. I have a list of Equipments (2nd sheet model/year/hours/prices/etc...) that gives me rounded value per year/model, but it's quite a large list. I'd like to set up (at the top of first sheet) a box where I can select from two drop list Models and years, and the rounded value would appear in a clean easy to read manner.

message edited by Shallak

Since we don't really know how your data is laid out, it's kind of hard to give a specific answer. Maybe this generic concept will point you in the right direction. Let's say this is your table on Sheet2:

A B C 1 Year Model Value 2 2013 Model A 100 3 2013 Model T 200First, insert a new Column A (often called a Helper Column) then put this formula in A1 and drag it down to the bottom of your list:

=B1&C1

You should have a table that looks like this:

A B C D 1 YearModel Year Model Value 2 2013Model A 2013 Model A 100 3 2013Model T 2013 Model T 200You may Hide Column A if you wish.

Now, back in Sheet1...

1 - Create a Drop Down For the Year in A1

2 - Create a Drop Down for the Model in B1

3 - Enter this formula in C1:=VLOOKUP(A1&B1,Sheet2!$A$2:$D$3,4,0)

This will VLOOKUP the combination of the values from the 2 dropdowns in the first column (A) of the table on Sheet2 and return the value in the 4th Column (the Value)

You may need to clean this up a bit to conceal any #N/A errors if a combination doesn't exist, but I'll leave that up to you.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

This is perfect! Thank you :D

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History