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

  • Thread starter Thread starter Steve Haack
  • Start date Start date
S

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
 
As long as the ranges are not dynamic:

A1 = Scott

=INDEX(INDIRECT("tbl" & A1 & "Scores"),MATCH.......
 
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.
 
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 -
Click to expand...
 
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.
 
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.
 
Back
Top