How to pull values from another sheet for dynamic Data validation

Question

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

Answer 1

Your formula is correct.
You just need to use single ' instead of double " quotes.

=INDEX('Value Reference'!B2:AJ6,MATCH(B4,'Value Reference'!A2:A6,0),MATCH(B5,'Value Reference'!B1:AJ1,0))

EDIT

Make Data validation dynamic

You can improve your results even further.
Instead of using List of items for your Data validation, you can use List from a range.

In order to do this, follow these steps:

1. In your Value Reference sheet

   • Insert a new column on the left of column A
   • In A1 use the formula =(TRANSPOSE(C1:AK1)) to get the Input Job Value

2. In your Main Sheet sheet for your Data validation use:

   • For cell B4 use List from a range and 'Value Reference'!B2:B7
   • For cell B5 use List from a range and 'Value Reference'!A1:A44

Now in cell B6 your formula becomes:

=INDEX('Value Reference'!C2:AK6,MATCH(D4,'Value Reference'!B2:B6,0),MATCH(D5,'Value Reference'!C1:AK1,0))

Answer 2

Try this in 'Main Sheet'!B6:

=query(transpose(query('Value Reference'!A1:AJ7,"Select * where A = '"&'Main Sheet'!B4&"'",-1)),"SELECT Col2 where Col1 = '"&'Main Sheet'!B5&"'",0)

---

2nd Solution :

    =QUERY(TRANSPOSE('Value Reference'!B1:AJ7),
    "SELECT Col"&filter(ArrayFormula(IF('Main sheet'!B4='Value Reference'!A2:A7,ROW('Value Reference'!A2:A7),)),
ArrayFormula(IF('Main sheet'!B4='Value Reference'!A2:A7,ROW('Value Reference'!A2:A7),))<>"")
    &" WHERE Col1 = '"&'Main Sheet'!B5&"'")

Using a Query is a good idea.
First the range: we use Transpose so we can use Col1 for example as reference instead of A type of selection.

The SQL is simple : Select the column with the corresponding city (Vancouver), then look for the row with the corresponding range (1001 - 2000)

The difficult part is to find the corresponding row in Value Reference of the B5 cell in the Main Sheet.

I used this ugly formula that returns 2,3,4,etc.. depending of the value in 'Main sheet'!B4:

filter(
ArrayFormula(IF('Main sheet'!B4='Value Reference'!A2:A7,ROW('Value Reference'!A2:A7),)),
ArrayFormula(IF('Main sheet'!B4='Value Reference'!A2:A7,ROW('Value Reference'!A2:A7),))<>"")   

Surely you can find another way to do this but this is the quick and dirty way.

The second part of the formula is pretty much the same, trying to select the only row where there is (1001 - 2000) in Col1. (Remember we transposed the original range).