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?
Best Answer
It doesn't need to be quite that complex. Take for example the following.
List
and Source to=Input1
B3
gets source=Input2
)=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 toVLOOKUP
except that instead of returning a cell/cell value it returns the relative position of thelookup_value
within thelookup_array
. Using it on theInput1
range returns the relative column position while using it onInput2
returns the relative row. Settingmatch_type
to 0 tells Excel to only return the exact match.INDEX(reference, row_num, [column_num], [area_num])
INDEX
is returning the cell inData
found at the referenced row ofInput2
and referenced column onInput1
.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.