This one is far out of my league.
To simplify things:
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
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
I'd like to create a cell (
B6) that shows the value of the selection from
B4 you select Surrey and
B5 you select 1000.
B6 inputs the value from Sheet2 that is from Row Surrey and Column 1000)
Here's my working code. Special thanks to @marikamitsos
=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)
'Value Reference'!A2:A15 and
Here is the link:
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.