Is there a way to repeat an IFERROR formula until achieving desired VLOOKUP?

[Defski]

I currently have a workbook which can have up to 52 of the exact formatted sheets.

Inside this workbook I have a data summery sheet that may need to reference any one of the 52 sheets based on values in column B.

Currently using This formula =IFERROR(VLOOKUP(B200,INDIRECT("'"&"wk"&D200&"'!"&"$G$3:$J$27"),4,FALSE),"") I can VLOOKUP data and refence a single sheet.

And a formula like this =IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&D201&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B197,INDIRECT("'"&"wk"&D197&"'!"&"$G$3:$J$27"),4,FALSE),"")) I can VLOOKUP data and refence in 2 Sheets. I don't think it would even be possible, but I would hate to have to do an IFFERROR 52 times to achieve the result I am looking for.

My question is, if I were to make a column with the name of each of my sheets, could I Use that list of names to IFERROR or something similar until it finds the match I am looking for?

 

[Defski]

This is the Function that ended up working for me....LOL

My question is there any way to simplify all the repeat IFERROR's (all 52 of them)? Something like adding a COUNTIF, or some other way to keep going to the next IFERROR referenced cell?

Any takers?


=IF(A201="","",IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$3&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$4&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$5&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$6&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$7&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$8&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$9&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$10&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$11&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$12&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$13&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$14&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$15&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$16&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$17&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$18&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$19&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$20&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$21&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$22&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$23&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$24&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$25&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$26&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$27&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$28&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$29&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$30&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$31&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$32&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$33&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$34&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$35&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$36&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$37&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$38&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$39&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$40&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$41&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$42&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$43&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$44&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$45&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$46&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$47&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$48&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$49&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$50&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$51&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$52&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$53&"'!"&"$G$3:$J$27"),4,FALSE),IFERROR(VLOOKUP(B201,INDIRECT("'"&"wk"&$AR$54&"'!"&"$G$3:$J$27"),4,FALSE),"Not Found")))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[RoryA]

Any takers?

Redesign your workbook?

 

[Fluff]

Assuming your sheets are named like wk1, wk2 etc & they are consecutive sheets in the workbook, how about

=IF(A201="","",VLOOKUP(B201,VSTACK('wk1:wk52'!$G$3:$J$27),4,0))

 

[Defski]

I assume using INDIRECT with this formula should work??? Currently I use only the number with the aid of INDIRECT to select the data on the desired Sheets.

Thanks, I am going to give that a try and will let you know how it goes.

 

[Fluff]

I assume using INDIRECT with this formula should work???

There is no need to use indirect.

 

[Defski]

I pasted in the formula, and it returns an #N/A error.

 

[Defski]

Possibly the issue is, although my sheets are in numerical order, I don't always have a sheet for the given week. so, my sheets might be something like /wk2/wk4/wk5/wk10/ and so forth.

 

[Fluff]

As long as you have wk1 & wk52 it doesn't matter if the others are there or not.