Return array from INDEX function in Excel?

Question

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

if the dates are in the sheet, you can use INDEX as ref for start and end. like: =INDEX(A:A,3):INDEX(A:A,7) would be the range (A3:A7). But if the values are generated inside the array, it will be easier to change your formula ore use VBA as you would need the whole array to get the {39618,39619,39638,39639} multiple times inside the formula — Dirk Reichel
– Dirk Reichel, Commented Jun 19, 2018 at 14:24
the dates are not in the sheet, they come from a formula. I am trying to avoid VBA, would like to do it using formulas only. The full formula is INDEX(qlTimeSeriesDates(A1),ROW(INDIRECT("2:"&qlTimeSeriesSize(A1)))) where A1 is a handle and ROW(INDIRECT("2:"&qlTimeSeriesSize(A1))) is just {2;3;4;...;N} — jsamler
– jsamler, Commented Jun 19, 2018 at 15:32
then either go for helper-cells or use a completely different approach or use vba... — Dirk Reichel
– Dirk Reichel, Commented Jun 19, 2018 at 15:33
Only if the order is always like your example ({smallest number, < x1 <, < x2 <, biggest number}) then you could use something like: SMALL({39618,39619,39638,39639},{2,3,4})... — Dirk Reichel
– Dirk Reichel, Commented Jun 19, 2018 at 15:51
thank you. this first index function i use will always be ordered (i.e. INDEX({39618,39619,39638,39639},{2,3,4})) but the second one (INDEX({18,19,38,39},{2,3,4})) will not necessarily. the elements {2,3,4} to be extracted will always be the same though. — jsamler
– jsamler, Commented Jun 19, 2018 at 17:14

ImaginaryHuman072889 · Accepted Answer · 2018-06-19 17:27:00Z

4

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

answered Jun 19, 2018 at 17:27

ImaginaryHuman072889

5,2857 gold badges23 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ImaginaryHuman072889 Over a year ago

@jsamler Please mark this answer as correct by placing a checkmark next to it.

Glitch_Doctor Over a year ago

Clever, I didn't even know of the N() formula... Thank you for sharing!

AYoungDukie Over a year ago

Is the use of the If({1},{2,3,4}) needed there, or could you write it as N({2,3,4})? It seems like removing the if statement allows the same behavior.

Collectives™ on Stack Overflow

Return array from INDEX function in Excel?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related