Return array from INDEX function in Excel?

问题:

I'm trying to use INDEX in array format in Excel but I'm running into problems.

From this question: Return array from INDEX function?, it seems that "INDEX (like VLOOKUP) doesn't return an array of values (except in some convoluted circumstances)"

So I'm wondering what alternatives there are.

I'm trying to do this:

=qlTimeSeries( ,
               INDEX({39618,39619,39638,39639},{2,3,4}),
               INDEX({18,19,38,39},{2,3,4})
               )

and Index is not returning an array.

Any alternative idea how to get the result I am trying to get with INDEX({39618,39619,39638,39639},{2,3,4})? i.e. from {39618,39619,39638,39639} get {39619,39638,39639} back?

NOTE: Instead of INDEX({39618,39619,39638,39639},{2,3,4}) my spreadsheet in fact uses a dynamic array of dates and the objective is to drop the first element of the array {39618,39619,39638,39639} which is why I am using INDEX.

EDIT: Looking for a solution that avoids using VBA

回答1:

Try this array formula:

= INDEX({39618,39619,39638,39639},N(IF({1},{2,3,4})))

This will return what you desire: {39619,39638,39639}.

Note this is an array formula, so you must press Ctrl+Shift+Enter on the keyboard after typing this formula instead of just pressing Enter.

(Also note this works with dynamic ranges, not just hard-coded arrays.)