Vlookup with Variable Table Array

[Bearpark]

I am trying to create a Vlookup to pull in data from various sheets. The formula below works for my purposes however I would like to have a variable table array as at present when copy the data to the next column for a new week number I have to manually type the week number for every single row *yawn*.

=IFERROR(VLOOKUP($C16,'Wk14'!$AA:$AC,3,FALSE),"")

I have tried to put Indirect formulae into the formula but either excel has a problem with me or (more likely) I have made a mistake:

=IFERROR(VLOOKUP($C19,Indirect(L15)$AA:$AC,3,FALSE),"") where L15 = the week number

I even tried a concatenate formula but don't know if this is even allowed for a variable lookup:

=IFERROR(VLOOKUP($C17,'"concatenate("Wk"&L15)"'!$AA:$AC,3,FALSE),"")

If somebody could please save my sanity it would be much appreciated, if you need further info let me know, thank you in advance!

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[BarryL]

try:

=VLOOKUP(C16,INDIRECT("'WK"&L15&"'!AA:AC"),3,0)

 

[Bearpark]

try:

=VLOOKUP(C16,INDIRECT("'WK"&L15&"'!AA:AC"),3,0)

- This returned a #VALUE error when I tried it, as suggested above giving some sample data might help.

I have a sheet for each week's data. Within each sheet there is a summary table in AA:AC that I use to ease the lookup. The value I am looking up is in AA and the value I would like returning is in AC.

C16 refers to a name which appear in AA.
L15 contains a number, the number is the week number that I am looking up.

The reason I added the IFERROR was that certain names do not appear in every week.

This is the first time I have posted on a forum like this so apologies if I am not being overly helpful with what I provided.

 

[BarryL]

- This returned a #VALUE error when I tried it, as suggested above giving some sample data might help.

I have a capital K in the it might be the problem

=VLOOKUP(C16,INDIRECT("'Wk"&L15&"'!AA:AC"),3,0)

 

[Bearpark]

I have a capital K in the it might be the problem

=VLOOKUP(C16,INDIRECT("'Wk"&L15&"'!AA:AC"),3,0)

This worked perfectly, I added some absolute formatting and it can now be moved along the columns effortlessly and will save me so much time! Thank you very much!

 

[BarryL]

This worked perfectly, I added some absolute formatting and it can now be moved along the columns effortlessly and will save me so much time! Thank you very much!

you're very welcome