VLOOKUP function over multiple sheets

[iglooman]

Hi,

I'm trying to create a VLOOKUP to find a value in the same data range on 12 different sheets (Months). How would I do this most effectively? I found this help post, but it seems to be limited to 3 sheets.

XL-CENTRAL.COM : Excel : Lookup a Value Based on a Single Criteria Across Multiple Sheets

Cheers,
Dan

 

[Oeldere]

use the search function in this forum and you will find a lot of hits.

http://www.mrexcel.com/forum/excel-questions/568463-vlookup-across-multiple-sheets.html

 

[iglooman]

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

=HLOOKAllSheets(E$3,$E$3:$AI$81,5,FALSE)

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
            Set Tble_Array = .Range(Tble_Array.Address)
 
                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

 

[Domenic]

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!