2

So I'd like, for various reasons (mostly to get equations further down the line to work) to call multiple ranges the same thing. Ok, doable in name manager. Lists is now defined as A1:A10,C1:C10. Typing in lists highlights both, great.

 =Index(Lists, 1) 

gives me the first cell in A,

=Index(Lists, 8) 

gives me the 8th cell, great.

=Index(Lists,11)

I'd expect to give me the 1st cell in C, however it gives me a #REF instead. How can I make it so that Index(Lists, 11) gives me the first number in C?

5
  • Index does not work with this reference you can copy C1:C10 and paste it under A10 starting A11
    – yass
    Apr 20, 2017 at 17:52
  • Yes, but if I'm going to be doing things automatically, there's not much point in what I'm doing. Trying to remove human interaction as much as possible. Current working solution is D1:D50 = A1:A50, and D51:D100 = C1:C50 - I'd like to avoid this if possible, but I might not be able to
    – Selkie
    Apr 20, 2017 at 17:55
  • 3
    What do you think about defining Lists1 A1:A10 and Lists2 C1:C10, and then =IF(NOT(ISERROR(INDEX(Lists1))), INDEX(Lists1), INDEX(Lists2)) Apr 20, 2017 at 18:00
  • Going to see if that'll work with what I want done further down the line... testing. Going to be interesting with multiple arrays inside of the same equation
    – Selkie
    Apr 20, 2017 at 18:02
  • 1
    And it works! Post your comment as an answer and I'll accept it. I wonder if an =iferror(index(list1),index(list2)) would be faster/better - and allow the solution to scale better when you have list3, list 4, etc.
    – Selkie
    Apr 20, 2017 at 18:12

0

You must log in to answer this question.

Browse other questions tagged .