Excel INDEX Function: Using a variable range for "Array"

Question

I am trying to use the INDEX function to search an array for a value. However I would like that array range to be variable. However when I use the Address function to try and specify the range, I get an #value! error. For instance:

=INDEX($A$1:$B$2,1,2) Returns the value I want.

=ADDRESS(1,1,1,TRUE)&":"&ADDRESS(2,2,TRUE) Returns $A$1:$B$2

However =INDEX(ADDRESS(1,1,1,TRUE)&":"&ADDRESS(2,2,TRUE),1,2) returns #VALUE!

Any help is appreciated. This excel sheet is going to be in the hands of someone else so I'm trying to avoid using macros.

How is the array determined... if you give more details, we may be able to propose a better solution than using ADDRESS. — BigBen
– BigBen, Commented Jun 11, 2020 at 21:24
I haven't abandoned this thread, I'm just thinking about teylyn's answer. — Jonathan Bray
– Jonathan Bray, Commented Jun 11, 2020 at 22:18

teylyn · Accepted Answer · 2020-06-11 21:43:36Z

1

Instead of Address, you can create a range with two Index functions

=INDEX($A:$Z,1,1):INDEX($A:$Z,2,2)

Wrap an Index around that

=index(INDEX($A:$Z,1,1):INDEX($A:$Z,2,2),1,2)

When you step through the formula with the Evaluate formula tool, you can see how the inner Index resolves into the range $A$1:$B$2.

answered Jun 11, 2020 at 21:43

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Excel INDEX Function: Using a variable range for "Array"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related