If the sheets are named as Sheet1,Sheet2 ....Sheet5 then try the below array
formula which lookup entry in C1 in cols A of 5 sheets and return the
matching value in ColB

=VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&
ROW(INDIRECT("1:5"))&"!A:A"),C1)0,0)&"!A:B"),2,0)

--
Jacob


"zee" wrote:

Am trying to lookup data from different worksheets in the single workbook.
Using vlook function but is very time consuming, doing the vlook for every
sheet.
can i do a vlookup for multiple sheets. or in other words add more then one
table arrays in the vlookup function?