How can I dynamically change the array reference in a vlookup formula

[TDunnam]

I have a range of data for a vlookup where the leftmost column needs to change based upon a value entered.

Ex: I have a team of baseball players (12 rows) and 20 games (20 columns) and within the r/c I've entered a position number. As the games pass the column reference moves. Here's a small version...

A B C D E
1 G1 G2 G3 G4 Names
2 1 4 3 2 Tom
3 2 1 4 3 Chris
4 3 2 1 4 Ethan
5 4 3 2 1 Jake
6
7 1 =vlookup(A7,A2:E5,5,FALSE) -- formula to pull names
8 2
9 3
10 4

=vlookup(A7,A2:E5,5,FALSE) this works great expect the Game column "A" needs to change... So I'm able to use the game number (G1) and calculate a new address by finding the starting column but I can't dynamically get it into the vlookup formula.

...some added info needed to calculate which game, column, address, etc.
11 G1 -- game number - manual input
12 =match(A11,A1:A5,0) --returns a 1 for starting column
13 2 --static value for the first row of data lookup
14 =match("Names",A1:A5,0) -- returns 5 for ending column
15 =sum(-A12,A14,1) -- the return column value - which works!

Here's my guess which is not working...
=vlookup(A7,concatenate(address(A13,A12,4),":E5"),A15,FALSE)
this should read for game 1 - G1
=vlookup(A7,A2:E5,A15,FALSE)

For game 2 - G2 - it should look like this:
=vlookup(A7,B2:E5,A15,FALSE)

When I look at the function argument box the table array is the #Value error. I'm assuming the vlookup doesn't recognize the concatenate as a reference but as text.

So how do I dynamically change the column for the left array element inside the vlookup formula?

Any help, suggestions, etc is greatly appreciated!

Oh -- and I'm trying not to write a macro - it's just not that complicated.

Thanks again,
Tom

 

[martindwilson]

=vlookup(a7,indirect(address(a13,a12,4)&":e5"),a15,false)

 

[AnAnalyst]

Hi there,

You basically need to use a dynamic named range combined with a cell that you can input the start cell and one that calculates the column offset of the result. So your formula will look something more like:

=vlookup(A7,Games,A15,0)

There's plenty of stuff on this forum about dynamic named ranges.

HTH

 

[TDunnam]

Thank you martindwilson for the input. It was driving me crazy! The INDIRECT option worked perfectly. I'll research what it is actually doing and keep it in my toolbelt of functions!

Thank you as well AnAnalyst. That was another thought and something I don't know how to do. I will definately look up dynamic name ranges to expand my excel knowledge.