Excel calculation based on user input from dropdown fields

microsoft excel

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?

enter image description here

Best Answer

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

Screen print of inputs and data table

  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.

Related Question