How can I shorten this VLookup formula?

[kellyfirth]

=IFERROR(VLOOKUP(A2,DAILYBBPT!A:B,2,0),IFERROR(VLOOKUP(A2,DAILYRSKIPPT!A:B,2,0),IFERROR(VLOOKUP(A2,DAILYWEBPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,WEEKLYRSKIPPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,WEEKLYWEBPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,BBDAILY!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYWIOF!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYBBCOPUSD!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYBBSINKINGPRICES!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYBBINDEXRATIO!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYRSKIPQUESADA!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,WEEKLYARMAJARO!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYALIQUOTGB!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,WEEKLYALIQUOTCF!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,WEEKLYALIQUOTPM!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,MONTHLYEMAILREQPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,QUARTERLYPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,MONTHLYRECSPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,MONTHLYRSKIPPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,MONTHLYTELPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,MONTHLYWEBPT!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,DAILYBBALLQ!A:B,2,0),"NA"),IFERROR(VLOOKUP(A2,BBALLQ0MIDPRICES!A:B,2,0),"NA")))

I'm trying to use this formula to perform a Vlookup and it technically works but obviously Excel doesn't allow this much text. Does anyone know how I could shorten it? I need to search all of the named sheets

 

[Aladin Akyurek]

If you are returning text...

Create a range housing the names of all of the relevant sheets. Select this range and the selection SheetList and invoke the following formula:

Control+shift+enter, not just enter:

=IFERROR(VLOOKUP(A5,INDIRECT("'"&LOOKUP(REPT("z",255),
  IF(COUNTIF(INDIRECT("'"&SheetList&"'!A:A"),A2),SheetList))&"'!A:B"),2,0),
  "N/A")