# VLOOKUP function over multiple sheets

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I've tried using it, gives me a 0 error. I changed it o HLOOKup as that is what I need: (I have a row of dates and I want the number in row 5 to show up when a date is found).

This is my VBA and formula
Code:
``=HLOOKAllSheets(E\$3,\$E\$3:\$AI\$81,5,FALSE)``

Code:
``````Function HLOOKAllSheets( _
Look_Value As Variant, _
Tble_Array As Range, _
Row_num As Integer, _
Optional Range_look As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'Use VLOOKUP to Look across ALL Worksheets and stops _
'at the first match found.
'With a small modification by Markmzz
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim hFound

Application.Volatile True

On Error Resume Next
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Name <> "Two Week" Then
With wSheet

hFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Row_num, Range_look)
End With
If Not IsEmpty(hFound) Then Exit For
End If
Next wSheet
Set Tble_Array = Nothing
HLOOKAllSheets = hFound
End Function``````

The array formula in the second example on that page can be adjusted to include all 12 worksheets. Simply list all your sheet names in a range of cells, and then adjust the formula accordingly. So, using the example on that page, if you list your sheet names in A2:A13, the formula would be as follows...

=VLOOKUP(C2,INDIRECT("'"&INDEX(A2:A13,MATCH(TRUE,COUNTIF(INDIRECT("'"&A2:A13&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,0)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Replies
4
Views
376
Replies
9
Views
436
Replies
6
Views
513
Replies
11
Views
676
Replies
1
Views
410

1,203,617
Messages
6,056,310
Members
444,858
Latest member
ucbphd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back