vlookup function
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. |
All times are GMT +1. The time now is 10:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com