INDEX/MATCH FORMULA

[marinpa]

I have this formula in excel and work just fine:

=INDEX(streets!$A$2:$D$16,,MATCH($A$1,streets!$A$1:$D$1,0))

Yet in gSheets does not work. Can someone help me with this?

Best regards

 

[KRice]

In Google Sheets, I have a source table on a sheet named "streets" and it looks like this (image at left):

On a different sheet, I have an entry in cell A1 (blue cell) that is used to query the source table on the "streets" sheet and return the column from the source table where A1 matches a column heading. The formula used in B1:

=INDEX(streets!$A$2:$D$16,,MATCH($A$1,streets!$A$1:$D$1,0))

Then I have a yellow cell that uses Data Validation (DV). The DV settings apply to cell A7 and specify to use "A list from a range" and the list is given by

=$B$3:$B

Note that I left the row off the end of the list range...Sheets will determine where the end is to return the entire list.

 

[marinpa]

I stated from the very beginning that formula works just fine in Excel.
The problem is that the very same formula does not work anymore., when imported into G Sheets
The question was if anyone can help the twik the formula to make it work in G Sheets as well

 

[KRice]

The snapshot in my last post was taken from Google Sheets...the formula works fine. I placed the formula in cell B1 and the results spilled down. @Fluff had asked for details about why you believe that it does not work and your answer mentioned a dropdown list. The INDEX/MATCH formula is used in a helper column to create a list of items. The dropdown validation then references that list. Are you trying to use the INDEX/MATCH formula directly in the Data Validation window, rather than referencing the list created by the formula?

 

[Fluff]

The problem is that the very same formula does not work anymore., when imported into G Sheets

You're formula works fine in Sheets, which I have already told you twice.
So what EXACTLY do you mean when you say it "doesn't work"? Do you mean that you do not get the drop down arrow in the data validation cell?