Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup from more than one Tab
Hi Experts,
Is there any way that vlookup can lookup value from more that one tabs. Refer following formula:- =vlookup(A1,(Sheet2!A:B,Sheet3!A:B),2,0) So my data is around 90,000 rows and I am using excel 2003. I want to lookup data from Sheet 2, Sheet 3 and so on.. Sheets count may increase to 50. Note: I have tried IF(IsError) and similar functions.. but they are limited in scope. -- Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup from more than one Tab
Hi,
Given you want to do many sheets then nesting a vlookup is parctical so a UDF. This will vlookup every sheet in your workbook until it finds and returns and answer/ Alt+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in. Call with =VlookAll(A1,B1:C20,2,FALSE) Where a1 is the value you are looking up B1:C20 is the lookup range, it can be as many columns as you want but is the same for every sheet 2 is the column to return and like Vlookup use TRUE or FALSE Function VlookAll(Lval As Variant, Tbl As Range, Cnum As Integer, TF As Boolean) Dim Sht As Worksheet Dim RetVal On Error Resume Next For Each Sht In ActiveWorkbook.Worksheets With Sht Set Tbl = .Range(Tbl.Address) VlookAll = WorksheetFunction.VLookup(Lval, Tbl, Cnum, TF) End With If Not IsEmpty(VlookAll) Then Exit For Next Sht End Function Mike "DILipandey" wrote: Hi Experts, Is there any way that vlookup can lookup value from more that one tabs. Refer following formula:- =vlookup(A1,(Sheet2!A:B,Sheet3!A:B),2,0) So my data is around 90,000 rows and I am using excel 2003. I want to lookup data from Sheet 2, Sheet 3 and so on.. Sheets count may increase to 50. Note: I have tried IF(IsError) and similar functions.. but they are limited in scope. -- Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup from more than one Tab
Hi,
Thought I'd rework it to make it work for only certain sheets. Now only works for sheets in MyArray Function VlookAll(Lval As Variant, Tbl As Range, Cnum As Integer, TF As Boolean) Dim MyArray() MyArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") On Error Resume Next For x = LBound(MyArray) To UBound(MyArray) With ActiveWorkbook.Sheets(MyArray(x)) Set Tbl = .Range(Tbl.Address) VlookAll = WorksheetFunction.VLookup(Lval, Tbl, Cnum, TF) End With If Not IsEmpty(VlookAll) Then Exit For Next End Function Mike "Mike H" wrote: Hi, Given you want to do many sheets then nesting a vlookup is parctical so a UDF. This will vlookup every sheet in your workbook until it finds and returns and answer/ Alt+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in. Call with =VlookAll(A1,B1:C20,2,FALSE) Where a1 is the value you are looking up B1:C20 is the lookup range, it can be as many columns as you want but is the same for every sheet 2 is the column to return and like Vlookup use TRUE or FALSE Function VlookAll(Lval As Variant, Tbl As Range, Cnum As Integer, TF As Boolean) Dim Sht As Worksheet Dim RetVal On Error Resume Next For Each Sht In ActiveWorkbook.Worksheets With Sht Set Tbl = .Range(Tbl.Address) VlookAll = WorksheetFunction.VLookup(Lval, Tbl, Cnum, TF) End With If Not IsEmpty(VlookAll) Then Exit For Next Sht End Function Mike "DILipandey" wrote: Hi Experts, Is there any way that vlookup can lookup value from more that one tabs. Refer following formula:- =vlookup(A1,(Sheet2!A:B,Sheet3!A:B),2,0) So my data is around 90,000 rows and I am using excel 2003. I want to lookup data from Sheet 2, Sheet 3 and so on.. Sheets count may increase to 50. Note: I have tried IF(IsError) and similar functions.. but they are limited in scope. -- Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup from more than one Tab
Hi there,
I can send you a Peo Sjoblom workbook that does that with a VLOOKUP formula. His example does eight workbooks, but you can add to it. You would adjust to your ranges and cells. The formulas look like this, with the first using a named range to list the worksheets and must be entered Ctrl +Shift + Enter, which will put curly brackets around the formula {the formula}. It returns the 3rd column of the lookup array, note the next to the last argument is a 3. The second formula simply lists all the worksheets (much longer) and is activated with Enter. It returns the 2nd column of the lookup array, note the next to the last argument is a 2. =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),3,0) =VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A2)0),0))&"'!A2:C200"),2,0) HTH Regards, Howard "DILipandey" wrote in message ... Hi Experts, Is there any way that vlookup can lookup value from more that one tabs. Refer following formula:- =vlookup(A1,(Sheet2!A:B,Sheet3!A:B),2,0) So my data is around 90,000 rows and I am using excel 2003. I want to lookup data from Sheet 2, Sheet 3 and so on.. Sheets count may increase to 50. Note: I have tried IF(IsError) and similar functions.. but they are limited in scope. -- Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup from more than one Tab
Hi,
You may also view solution 9 at the following link - http://ashishmathur.com/knowledgebaseII.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DILipandey" wrote in message ... Hi Experts, Is there any way that vlookup can lookup value from more that one tabs. Refer following formula:- =vlookup(A1,(Sheet2!A:B,Sheet3!A:B),2,0) So my data is around 90,000 rows and I am using excel 2003. I want to lookup data from Sheet 2, Sheet 3 and so on.. Sheets count may increase to 50. Note: I have tried IF(IsError) and similar functions.. but they are limited in scope. -- Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |