Excel calculation based on user input from dropdown fields

Question

We are using Excel 2003 but can upgrade if necessary. We need to compute (or lookup) a value based on two inputs. Both inputs will be Excel dropdown boxes.

The first one will have values 6, 9, 12, 16, 20. The second dropdown has 6x6, 10x10, 20x20.

How can we lookup the corresponding value and place the result into a separate cell?

Answer 1

It doesn't need to be quite that complex. Take for example the following.

1. Select your column headers and name them Input1. (Range E1:I1 in the example.)
2. Select your row headers and name them Input2. (Range D2:D4)
3. Select your data and name it Data. (Range E2:I4)
4. Use data validation to get the dropdowns.
   • Select cell B2 and click on Data - Data Validation.
   • Set Validation criteria Allow to List and Source to =Input1
   • Do whatever you want for Input Message and Error Alert. (I turned them off.)
   • Repeat for your 2nd input. (cell B3 gets source =Input2)
5. In your Result cell, type in this formula =INDEX(Data,MATCH(B3,Input2,0),MATCH(B2,Input1,0))

The formula works as follows:

MATCH(lookup_value, lookup_array, [match_type])

MATCH is sort of similiar to VLOOKUP except that instead of returning a cell/cell value it returns the relative position of the lookup_value within the lookup_array. Using it on the Input1 range returns the relative column position while using it on Input2 returns the relative row. Setting match_type to 0 tells Excel to only return the exact match.

INDEX(reference, row_num, [column_num], [area_num])

INDEX is returning the cell in Data found at the referenced row of Input2 and referenced column on Input1.

Note: In Excel 2010 (I'm not sure about earlier versions) the data table doesn't even need to be on the same worksheet as your Input and Result cells.

Answer 2

There is a way to do this, but it is not as intuitive as you would think. In order to accomplish what you want, you can use the same type of table layout you have there, but you'll need to add a bit more too it:

• User Data Table: This can be the same format you already have, with the dimensions on the left side, and type/sizes on the top, and the relevant data where it goes.

• Form Data and Indexes: Because you have combo boxes, i.e. form controls, on the excel sheet, you need to be able to properly bind that information. To do this, check the bind section of this answer at the bottom; but suffice it to say, you need to tell the form what dimensions and what types you have, so that they appear in the combo boxes. You also need to tell the excel sheet what indexes are to what value, as the combo boxes do not return any data, other than actual index.

• Results and Formula: User can select the dimensions and types they want, and you can see what the data is; the result is also computed with the formula listed there. You can see from the data, how the formula is put together. You would need to customize the ranges, and locations based on y our sheet, and where you put everything, but that is why i gave you the breakdown section.

---

Adding ComboBoxes to Excel:

• Click Developer at the top, or use the Developer menu in earlier versions:

If you do not have a Developer tab, or menu at the top, edit your preferences, usually in the advanced section, and check, or enable the Developer option, so you have the menu. You can check this article here, for more detail: http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx

• Click Insert, and then choose the Combo Box:

That will add the actual combo box to your excel sheet, usually, unless you draw the size you want, you are given one big box:

You can re-size the combo box, by right clicking on it, and dragging the edges as necessary.

• Right Click on the new combo box, and choose Format Control:

• Configure the options the way you need:
  • Input Range: These should be a vertical list of all options to be in the combo box
  • Cell Link: This is where you want the index result to be returned.

• Results will then return as you work with the combo box:

• To Bind an index to a value: Simply specify index counts after your combo box options, and do a simple lookup: