Indirect Alternative

[par0016]

I recently discovered =INDIRECT and have been using it on one of my large spreadsheets with great success. My only problem is that I have found that it is very volatile and has slowed down my spreadsheet to a crawl. I am wondering if there is some kind of non-volatile alternative to =INDIRECT.

To better explain how I am using it, I made a small spreadsheet that mimics what I use it for: Indirect.xlsx

In the spreadsheet, I have each of the 3 lists set as named ranges (Ctrl+F3 to see them). I used Indirect so that I can type the name of the List that I want in cell C2 and it will display that list below it. I know that =INDIRECT doesn't slow down this spreadsheet at all but the one I am using it on is much larger ranging over several tabs. Also if it matters, my "list" on my other spreadsheet are sometimes on other tabs and at different row heights than where I am outputting their values.

Any help would be great. Thanks.

[shg]

In C3,

=INDEX($F$3:$H$12, B3, MATCH(C$2, $F$2:$H$2, 0))

[par0016]

That works great if all of the lists are on the same sheet and inline. But more accurately my spreadsheet would look closer to something like this:

Indirect2.xlsx

where list 1 and 2 are on Sheet2 with a space between them and offset and List 3 is on Sheet3

[shg]

To better explain how I am using it, I made a small spreadsheet that mimics what I use it for

In C3,

=INDEX($F$3:$H$12, B3, MATCH(C$2, $F$2:$H$2, 0))

That works great if all of the lists are on the same sheet and inline. But more accurately my spreadsheet would look closer to something like this:

You mean it answers the question you asked, not the one you didn't? Sorry, my fault.

For the latter approach, which is just a bad design, you're probably stuck with INDIRECT.

[par0016]

Also if it matters, my "list" on my other spreadsheet are sometimes on other tabs and at different row heights than where I am outputting their values.

I did have that in my original post

For the latter approach, which is just a bad design, you're probably stuck with INDIRECT.

and you don't need to be a mean about it. I am just asking for help with a problem.

[shg]

Is the second example really how your workbook is designed?

[par0016]

I didn't design it like that, I am compiling other people's data that is all formatted differently into one spreadsheet. Why am I even telling you this as you are obviously more interested in ridiculing than helping?

[shg]

Something like

=index(choose(match(listNameAtTop, {"List1","List2","List3"}, 0), List1, List2, List3), indexValue)

... where List1/2/3 are dynamic range names.