Selected Answer
I would use this code:
Function VLOOKUPWORKBOOK(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
'Vlookup function that will search all worksheets in the workbook - however, the data table that is being searched must be in the same
'location on every worksheet.
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet
Set Tble_Array = Nothing
VLOOKUPWORKBOOK = "Sheet " & wSheet.Name & " contains the number."
End Function
This is a macro and is modified from our macro (UDF) here: Vlookup across the entire workbook.
To better understand the macro, read through the link. The main thing is that the data has to be in the same location on each sheet.
Also, to learn more about UDF's (user defined functions) read our tutorial on UDF's.