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