This one is far out of my league.
To simplify things:
Sheet1
B4
has a Data validation drop down list Ex. Vancouver, Surrey, Burnaby, etc
B5
has a Data validation drop down list Ex. 500, 1000, 2000, etc
Sheet2
Has Rows named Vancouver, Surrey, Burnaby, etc
Has Columns named 500, 1000, 2000
The cross reference cells between the rows and columns names have numerical values
On Sheet1
I'd like to create a cell (B6
) that shows the value of the selection from B4
and B5
.
(Ex. B4
you select Surrey and B5
you select 1000. B6
inputs the value from Sheet2 that is from Row Surrey and Column 1000)
FINAL EDIT
Here's my working code. Special thanks to @marikamitsos
Code: =INDEX('Value Reference'!B2:AJ6,MATCH(B4,'Value Reference'!A2:A15,0),MATCH(B5,'Value Reference'!B1:BR1,0))
I did remove the data validation "list of item" and changed it to "List from a range" (which works much better)
Code: 'Value Reference'!A2:A15
and 'Value Reference'!B1:BR1
Here is the link:
https://docs.google.com/spreadsheets/d/1Ca-buLW7i7lt8Lxw3AshDumm3g_HUPmMD2L2ZWeBj2c/edit#gid=0
TRANSPOSE
. Why do you think that we need this tag? Actually the current revision of this question doesn't mention TRANSPOSE so the title should be updated.