It doesn't need to be quite that complex. Take for example the following.
- Select your column headers and name them Input1. (Range E1:I1 in the example.)
- Select your row headers and name them Input2. (Range D2:D4)
- Select your data and name it Data. (Range E2:I4)
- 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
- Do whatever you want for Input Message and Error Alert. (I turned them off.)
- Repeat for your 2nd input. (cell
B3 gets source
- In your Result cell, type in this formula
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
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.