Assuming that on each sheet, Sheet1 through Sheet5, A2:B100 contains the lookup table, download and install the free add-in
Morefunc.xll, and use the THREED function, something like...
=VLOOKUP(D2,THREED('Sheet1:Sheet5'!$A$2:$B$100),2,0)
...where D2 contains the lookup value.. Otherwise, using built-in functions only, let G2:G6 contain a list of sheet names and try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...
=VLOOKUP(D2,INDIRECT("'"&INDEX($G$2:$G$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$6&"'!A2:A100"),D2)>0,0))&"'!A2:B100"),2,0)
Hope this helps!