Vlookup from more than one Tab

[DILipandey]

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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Mike H]

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

 

[Mike H]

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

 

[L. Howard Kittle]

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";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard

 

[Ashish Mathur]

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