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]

I do not see any issues. When you say "does not work", what do you mean? Do you get a result, just not the expected result. Or do you see an error? What is the error?

What is in cell $A$1 and what is in cells streets!$A$1:$D$1? Are you certain that an exact match exists?

 

[nageswararao]

I didn't find wrong with it.

 

[marinpa]

In excel it works just fine, but when I import the excelsheet into gSheets, does not work anymore


in sheet "streets"I have 4 Cities
A1,B1, C1, D1
A2:A20 Streets of city in A
B2:B15 Streets of city B
C2:C25 streets city of C
D2:D16 streets city D

INDEX should be =INDEX(streets!$A$2:$D$16
MATCH should be A1:D1 (cities row)


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

In excel works just fine, problem occurs when imported into gSheets

 

[Fluff]

In what way doesn't it work in Sheets?

 

[marinpa]

Doesn't show up any streets in the drop down

 

[Fluff]

That has nothing to do with your original question.
There is nothing wrong with the formula, it's just that when you use a formula in Sheets validation, you don't get an in cell drop down arrow.

 

[marinpa]

That has nothing to do with your original question.
There is nothing wrong with the formula, it's just that when you use a formula in Sheets validation, you don't get an in cell drop down arrow.

This have everything to do with my original question. I said formula works just fine in Excel but not in sheets. Maybe someone knows something that would do the trick to work in sheets as well

 

[Fluff]

But the formula works perfectly well in sheets, including in data validation (which you never mentioned).

 

[KRice]

Doesn't show up any streets in the drop down

The point is your original post asked about the formula. The formula works fine...it delivers expected results. The problem is that you apparently want to use this formula to produce a list that is then available via a drop down selector. Does that sound correct?