Column Index Relative Value

[prudential]

Hi,

Does anyone know how to make the column_index not absolute for copying Vlookups across columns?

The 2nd last line shows A2:T31 when it should be A33:T62.

It would be very time consuming to change all the cells manually.

thanks.

=IF($C$61=1,VLOOKUP($A$61,'Team Stats (L1)'!A2:T31,2,FALSE),
IF($C$61=3,VLOOKUP($A$61,'Team Stats (L3)'!A2:T31,2,FALSE),
IF($C$61=5,VLOOKUP($A$61,'Team Stats (L5)'!A2:T31,2,FALSE),
IF($C$61=10,VLOOKUP($A$61,'Team Stats (L10)'!A2:T31,2,FALSE),
IF($C$61="S",VLOOKUP($A$61,'Team Stats (Season)'!A2:T31,2,FALSE),
IF($C$61="A",VLOOKUP($A$61,'Home Away Stats'!A33:T62,2,FALSE),
IF($C$61="A15",VLOOKUP($A$61,'Home Away Stats (L15)'!A33:T62,2,FALSE),
IF($C$61="A10",VLOOKUP($A$61,'Home Away Stats (L10)'!A2:T31,2,FALSE),
IF($C$61="A5",VLOOKUP($A$61,'Home Away Stats (L5)'!A33:T62,2,FALSE))))))))))

[Glenn Kennedy]

I'm not sure if I follow your question. A2:T31 is the Table array (an without any $ signs is already not absolute). The column index is 2 (and is absolute). Maybe it's just me, but a sample worksheet, posted here, showing the problem would be easier to understand...

[prudential]

Hi Glenn,

Its just the A2:T31 in that last row for all cells that need changing. Im pretty good with the replace function in excel and notepad but i dont know to make this one change.

Sorry if i confused you, the table arrays should be absolute values also.

...and if i make everything absolute values, that would work but the problem is the column index doesnt change and i need that to change when i copy and paste.

[Glenn Kennedy]

If I understand you correctly (this time!!!), try this

http://www.excelforum.com/excel-new-...-formulas.html

suitably adjusted for your own circumstances (much easier for you - 3 steps, I think).

[prudential]

Glenn,

I just want to change that one table array for all cells but to use the replace function I will need the "A10" so I will need the whole line to find and replace because the A2:T31 table array is needed elswhere in the formula. The only way i can distibguish it is by including the A10

IF(C61="A10",VLOOKUP(A61,'Home Away Stats (L10)'!A2:T31

with

IF(C61="A10",VLOOKUP(A61,'Home Away Stats (L10)'!A33:T62

thanks.

P.S. Sorry, i couldnt work out how to apply the method you provided in your last message. to my situation.

[Glenn Kennedy]

Sorry... and Yes. There is no need for any use of wildcards in your case. Just hit CTL H and go for it.

[prudential]

Yeeehah, I worked it. It accepted the whole string. Thanks for your help!