Here's another way...

Assuming that for each of your five sheets Columns A and B contain your
lookup table, try the following formula (in a separate sheet)...

=VLOOKUP(A1,INDIRECT("'"&INDEX(B1:B5,MATCH(TRUE,CO UNTIF(INDIRECT("'"&B1:B
5&"'!A:A"),A1)0,0))&"'!A:B"),2,0)

....where A1 contains your lookup value and B1:B5 contains your list of
sheet names. The formula needs to be confirmed with
CONTROL+SHIFT+ENTER. If Column B contains numerical values, you could
use the following formula instead...

=SUMPRODUCT(SUMIF(INDIRECT("'"&B1:B5&"'!A:A"),A1,I NDIRECT("'"&B1:B5&"'!B:
B")))

....confirmed with just ENTER.

Hope this helps!


In article ,
"Rishab shah" <Rishab wrote:

I have data in my 5 sheets in the same work book.

Can vlookup command find the data from the 5 sheets.