Google Sheets Arrayformula(Index(A:A,{3,4,5})) Get Multiple Rows?

[excelroofing]

Let's say A1:A10 is 1-10. I put =Arrayformula(Index(A:A,{3,4,5})) in B1, expecting to get the values in rows 3, 4, and 5 in B1:B3. But all it returns is "3" in B1. Is INDEX not able to return multiple rows using arrayformula and curly brackets?

[Haluk]

Seems INDEX does not like ARRAYFORMULA but you can use this;

Please Login or Register  to view this content.

[excelroofing]

I was just using those numbers as placeholders for simplicity, but in reality it would be other data, like dates for example. I've attached a CSV you can open in Excel or any spreadsheet program to show you something closer to the real data.

A:A is dates, B:B is temperatures. C2 is the date to search for. D2 is the matching row number for that date =MATCH(C2,A:A,0). E2 is the formula to return temperatures for that date and all 4 days after that date =ARRAYFORMULA(INDEX(B:B,{D2,D2+1,D2+2,D2+3,D2+4})).

Have a look at the CSV, or here is an image if that's easier, and thanks!

Index Question.PNG

[janmorris]

Hi excelroofing.

First you wanted the 3rd,4th, and 5th values,

Now you want the 8th,9th,10th,11th, and 12th values.

So it seems that your conditions are changing.

It would be a lot easier for us to help you if you could provide a link to a sample file (you can do that now because you have 10+ posts) and clearly explain your true objective.

You can use this link to generate a Google Sheets file that you can share anonyously:
https://docs.google.com/forms/d/e/1F...VbWiA/viewform

Note: sharing CSV files is useless because we have to convert it to some other file type that can use formulas.