How do I use a variable as a reference to an array in INDEX

[Steve Haack]

I want to use the INDEX function to lookup data in an array. I have several
ranges defined as tables, and have named them tblScottsScores,
tblStevesScores, etc.

I want to use INDEX to lookup data in those tables, which I can do when I
use those table names as the first parameter in the INDEX formula.

What I need to do though, is not hardcode the name of the table in the INDEX
formula. I want to build it on the fly by reading the person's name from
another table and then concatenating it with text to create the name of the
table. For example, I want to do something like this (the name Scott is in
Cell A1, for simplicity):

INDEX("tbl" & A1 & "Scores", MATCH.......

What is the syntax for me to be able to build that array name for the first
parameter in the INDEX formula?

Thanks,
Steve

 

[T. Valko]

As long as the ranges are not dynamic:

A1 = Scott

=INDEX(INDIRECT("tbl" & A1 & "Scores"),MATCH.......

 

[Steve Haack]

Well, I guess they would be dynamic. I will be looking the name of the person
up from another table. So, I would be using a different INDEX formula to get
the name of the person and then building the text tring from that. So, using
a named range or a cell reference won't help me.

 

[Pete_UK]

Did you try Biff's approach?

Pete

Well, I guess they would be dynamic. I will be looking the name of the person
up from another table. So, I would be using a different INDEX formula to get
the name of the person and then building the text tring from that. So, using
a named range or a cell reference won't help me.







- Show quoted text -

 

[T. Valko]

As long as the ranges are not dynamic

Let me clarify.

As long as the named range "tblNameScores" isn't a dynamic range defined
using other functions like OFFSET.

 

[Steve Haack]

Biff,
That worked. Thanks so much for the help. When you said "dynamic" I was
thinking that since I was working with a Table, and the number of rows
changes all the time, that was what you meant by dynamic. I understand now
what you meant.

Thanks again.

 

[T. Valko]

Ok, good deal. Thanks for the feedback!