Lookup across multiple sheets (40+)

Microsoft Excel 2003 (full)
July 13, 2010 at 02:01:24
Specs: Windows XP
I'm trying to create a lookup to search for one of our batch numbers, which could be on any of over 40 sheets within one workbook.
Using the numeric batch number the entire workbook needs to be searched and then return the contents of an adjacent cell (which is decided by column number and can be text,numeric or both).
I don't want to have to input over 40 sheet name! Can anyone help?

See More: Lookup across multiple sheets (40+)

Report •

July 13, 2010 at 05:31:43
Do you need to search the entire contents of every sheet for the desired batch number or would it be in a specific (and same) column on every sheet?

Report •

July 13, 2010 at 05:41:12

As you have 40 sheets to search, I suggest you try the user defined function that is similar to VLOOKUP() but also accepts a range of worksheets as well as a range of cells.

Response #3 in this post has the function and how to install and use it.


Report •

July 22, 2010 at 01:12:18
The UDF mentioned isn't working for me. I believe that the problem is that all the sheets have completely different names ( they do not follow a series sht1, sht2 etc).

Yes the batch numbers do appear in only one column on every sheet (B).

Report •

Related Solutions

July 22, 2010 at 07:08:30

Two points:
1. The worksheets do not need to be in a 'sequence' such as St1, St2 etc.
A first sheet named Hinge and a last sheet named Bracket is OK, even if the ones in between are named Screw, Hammer and Nail.

It all depends on the the worksheet tab names displayed at the bottom of the main Excel window.

Move any tabs that you don't want to include to the start of the Tabs and then use the first tab name that you want to use, as the start of the range, and the rightmost one as the last.

2. saying The UDF mentioned isn't working for me isn't very helpful.
Does it do nothing, is there an error message in the cell. Do you get the wrong answer.

If you get the wrong answer can you come to some conclusion as to why its wrong, did it miss some sheets, did it include a sheet it shouldn't etc...
Can you also post the formula you used, by copying it from the formula bar, so I can see the parameters applied.


Report •

July 23, 2010 at 00:49:34
Just to check i reentered the UDF and formula and it's working. I may have adjusted the formula incorrectly last time.

But now it's sorted.

Humar you're a star. Thanks so much.

Report •

July 23, 2010 at 04:28:26

Report •

July 23, 2010 at 05:37:09

Glad it works.

I just looked at the UDF again, and it should have this line, after Dim n as integer:


The reason for this is that Excel formulas recalculate automatically if any cell referenced in the formula changes.

For example SUM(A1:A10) will automatically recalculate if any cell in the range A1 to A10 changes.

In this UDF the ranges on the other worksheets are not explicitly referenced. This means that the formula works when you enter it, or when you select the cell and click enter, but it will not update if a cell on one of the included worksheets changes.

Application volatile, means that the UDF will recalculate when any cell changes.
This adds a small overhead to running Excel, but as long as you don't have hundreds of cells containing this UDF, the effect is negligible.

Here is the full UDF with a minor bug correction:
The function now returns #NA (like VLOOKUP) when the search item is not found.

#VALUE is returned if the code creates an error

Option Explicit

Public Function MultVlookup( _
                    FindThis As Variant, _
                    LookIn As Range, _
                    SheetRange As String, _
                    OffsetColumn As Integer, _
                    Optional ReturnAddress As Boolean = False) _
                As Variant

Dim Sheet As Worksheet
Dim strFirstSheet As String
Dim strLastSheet As String
Dim SheetArray() As String
Dim blnFirstSheet As Boolean
Dim rngFind As Range
Dim blnFound As Boolean
Dim intSheets As Integer
Dim n As Integer

'make function recalculate with all changes in data
'else it won't respond to changes in cells on the other worksheets

'make search range one column
If LookIn.Columns.Count > 1 Then
    Set LookIn = LookIn.Resize(LookIn.Rows.Count, 1)
End If

'size array to hold all worksheet names
ReDim SheetArray(ActiveWorkbook.Worksheets.Count)

'get the two worksheet names
strFirstSheet = Left(SheetRange, InStr(1, SheetRange, ":") - 1)
strLastSheet = Right(SheetRange, _
                Len(SheetRange) - InStr(1, SheetRange, ":"))
'put worksheet names in the range 'Sheet Range' into an array
blnFirstSheet = False
n = 0
For Each Sheet In ActiveWorkbook.Worksheets()
    If Sheet.Name = strFirstSheet Then
        blnFirstSheet = True
    End If
    If blnFirstSheet = True Then
        SheetArray(n) = Sheet.Name
        n = n + 1
    End If
    If Sheet.Name = strLastSheet Then
        blnFirstSheet = False
    End If
Next Sheet
'save number of sheets
intSheets = n

'search range on each worksheet in array
blnFound = False
For n = 0 To intSheets - 1
    With Worksheets(SheetArray(n)).Range(LookIn.Address)
        Set rngFind = .Find(FindThis, LookIn:=xlValues, _
                        MatchCase:=False, LookAt:=xlWhole)
    End With
    If Not rngFind Is Nothing Then
        'match found
        blnFound = True
    End If
    If blnFound = True Then Exit For
Next n

'return value
If blnFound = True Then
    If ReturnAddress = False Then
        'just return the value
        MultVlookup = rngFind.Offset(0, OffsetColumn - 1)
        'return the address
        MultVlookup = SheetArray(n) & "!" & _
            rngFind.Offset(0, OffsetColumn - 1).Address
    End If
    MultVlookup = CVErr(xlErrNA)
End If
End Function


Report •

July 23, 2010 at 06:13:59
That's even better and it works like a charm. Updating like that didn't even occur to me.
Thanks. you've anticipated a problem before I realised it was there.


Report •

Ask Question