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

Can vlookup command find the data from the 5 sheets.

No. But you could look in each sheet until you find it.

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,0))),VLOOK UP(A1,Sheet2!A:B,2,0),
IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,0))),VLOOKU P(A1,Sheet3!A:B,2,0),
IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKU P(A1,Sheet4!A:B,2,0),
"missing from all 3")))

(I stopped after looking at 3 sheets. You could continue until you try to add
the 8th lookup--excel can nest 7 functions.)

Rishab shah wrote:

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

Can vlookup command find the data from the 5 sheets.

--

Dave Peterson

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.