3

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

12
  • Have you tried the solutions offered? Did any work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?) Sep 11, 2019 at 8:40
  • @marikamitsos while we have tags for some Google Sheets functions, they are about functions that are complex and the site already had several questions about how to use them. AFAIK the site hasn't many questions specifically about 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.
    – Rubén
    Sep 11, 2019 at 14:02
  • @Rubén "AFAIK the site hasn't many questions specifically about TRANSPOSE. Why do you think that we need this tag?". I don't. I didn't put it there. Found it and didn't check it. It's a good thing you did. Sep 11, 2019 at 14:05
  • @marikamitsos I'm sorry. It was added by another editor. That revision shouldn't be approved.
    – Rubén
    Sep 11, 2019 at 14:07
  • 1
    @Rubén Original title didn't make much sense. Also, there was just a minor mistake on the original formula. Not worthy of a title. I think making Data validation dynamic is more important. But "INDIRECT"? What on earth was I thinking? Please improve it. Sep 11, 2019 at 14:35

2 Answers 2

2

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))

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.