Solved Vlookup over multiple sheets

June 24, 2010 at 11:41:57
Specs: Windows Vista
Hi there. This has been bugging me for ages..hope someone can help.

I have created a tracker that records absences at work. Each team has their own individual work sheet (11 in total). Column B contains their name and Column C contains the number of instances they have had off.

The summary page, contains the name of every employee. I want to use a vlookup to search the different tabs and return the value in column B

How can I do this?

appreciate any help


See More: Vlookup over multiple sheets

Report •


✔ Best Answer
September 10, 2010 at 13:57:52
Hi,

The problem is that the MultVlookup function was designed to stop after finding the first match.

You correctly changed the logic to allow it to loop repeatedly, i.e. not stop after finding the first match, but the Find function does not stop when it has gone through the range specified - it just starts again, so you need to add a test to show that it has got back to where it started from - in practical terms if it finds a matching cell that is the one it found first of all.
Secondly, Find always starts with the cell after the first cell in the range passed to it. The accepted way to repeat Finds with each find starting after the last cell matched is to use FindNext - but this does not work when called from a UDF. As a result you have to use repeated Finds and manage the starting addresses each time.

In your case because you want multiple 'finds', the code I proposed in VlookupMin is more appropriate as a starting point.
You will see that this saves an address and uses it as a test in the Do Until loop, the loop stopping when the address found matches the saved address of the first matching cell.

I made a few mods to the VlookupMin and this is what I came up with:

Public Function MultVlookupW( _
                    FindThis As Variant, _
                    LookIn As Range, _
                    SheetRange As String, _
                    OffsetColumn As Integer) _
                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 numWins As Integer
Dim intSheets As Integer
Dim strLastAddr As String
Dim n As Integer

'make this function volatile, as the addresses of the cells on
'all worksheets are not included in the function's parameters
'and the function will not recalculate if either the data in the
'search range changes, or the data in the found cell changes.
Application.Volatile

'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 - 1

'initialize numWins
numWins = 0

'search range on each worksheet in array
For n = 0 To intSheets
    'first 'find' on this worksheet
    With Worksheets(SheetArray(n)).Range(LookIn.Address)
        Set rngFind = .Find(FindThis, LookIn:=xlValues, _
                        MatchCase:=False, LookAt:=xlWhole)
        If Not rngFind Is Nothing Then
            'save first matching cell address -
            'so we can stop find's wrap around
            strLastAddr = rngFind.Address
            'and increment numWins if offset cell value is "W"
            If rngFind.Offset(0, OffsetColumn - 1).Value = "W" Then
                numWins = numWins + 1
            End If
        End If
        'repeat find until no more matches on this worksheet
        Do Until rngFind Is Nothing
            'set search range start to last found address
            Set rngFind = .Find( _
                        FindThis, _
                        LookIn:=xlValues, _
                        After:=rngFind, _
                        MatchCase:=False, _
                        LookAt:=xlWhole)
            'stop if Find is back at start
            If rngFind.Address = strLastAddr Then
                Set rngFind = Nothing
                Else
                'increment numWins if offset cell value is "W"
                If rngFind.Offset(0, OffsetColumn - 1).Value = "W" Then
                    numWins = numWins + 1
                End If
            End If
        Loop
    End With
Next n

'return value
MultVlookupW = numWins
End Function

I called it MultVlookupW only because I already have a MultVlookup and can't have two in the same copy of Excel.

Hope this does what you were looking for.

Regards



#1
June 24, 2010 at 15:52:22
Hi,

I have a solution that works in Excel 2003 with almost any number of worksheets, as it does not involve nesting formulas (limited to 7 levels in Excel 2003). VLOOKUP() would have to be nested 11 times to work, but as you can't nest 11 times, a different approach is required.

On each worksheet:
Row 1 contains headers (if required - else leave empty)
Row 2 (above the first entry) must be empty
Row 3 contains the first entry
Names are in Column B and numbers of absences are in Column C. (These could be changed - but for this example that's what I used).

It is preferable that the 11 worksheets have short names, only to reduce the overall length of the formula! Remember don't use worksheet names that look like cell references, e.g. S1

On Sheet1 (can be any name, e.g. "Summary"), there is a list of all names. For this example they are in column A, but this can be changed.
With the first name in cell A1:

This is the formula to put in cell B1, if your worksheets are named MyS1 to MyS11
=OFFSET(MyS1!$B$3,SUMPRODUCT((MyS1!$B$3:$B$52=A1)*(ROW(MyS1!$B$3:$B$52)-2))-1,1) & OFFSET(MyS2!$B$3,SUMPRODUCT((MyS2!$B$3:$B$52=A1)*(ROW(MyS2!$B$3:$B$52)-2))-1,1) & OFFSET(MyS3!$B$3,SUMPRODUCT((MyS3!$B$3:$B$52=A1)*(ROW(MyS3!$B$3:$B$52)-2))-1,1) & OFFSET(MyS4!$B$3,SUMPRODUCT((MyS4!$B$3:$B$52=A1)*(ROW(MyS4!$B$3:$B$52)-2))-1,1) & OFFSET(MyS5!$B$3,SUMPRODUCT((MyS5!$B$3:$B$52=A1)*(ROW(MyS5!$B$3:$B$52)-2))-1,1) & OFFSET(MyS6!$B$3,SUMPRODUCT((MyS6!$B$3:$B$52=A1)*(ROW(MyS6!$B$3:$B$52)-2))-1,1) & OFFSET(MyS7!$B$3,SUMPRODUCT((MyS7!$B$3:$B$52=A1)*(ROW(MyS7!$B$3:$B$52)-2))-1,1) & OFFSET(MyS8!$B$3,SUMPRODUCT((MyS8!$B$3:$B$52=A1)*(ROW(MyS8!$B$3:$B$52)-2))-1,1) & OFFSET(MyS9!$B$3,SUMPRODUCT((MyS9!$B$3:$B$52=A1)*(ROW(MyS9!$B$3:$B$52)-2))-1,1) & OFFSET(MyS10!$B$3,SUMPRODUCT((MyS10!$B$3:$B$52=A1)*(ROW(MyS10!$B$3:$B$52)-2))-1,1) & OFFSET(MyS11!$B$3,SUMPRODUCT((MyS11!$B$3:$B$52=A1)*(ROW(MyS11!$B$3:$B$52)-2))-1,1)

This formula is dragged down column B alongside all the names in column A on the Summary worksheet.

Due to the size of the formula you will need to click on a cell lower down column B, then use arrow keys to move up to B1 and use the menu to select Edit - Copy.
Hold down the Shift key and select the last cell in column B next to a name in column A and select Paste.

Column B will now show the absence value for each 'name', whichever of the worksheets the name is on.
If two names are the same they will show up as two values in the cell - there is no check in the formula for duplicates.

******************************************
Creating the full formula from scratch:

If you use different worksheet names, as the formula is very repetitive, I suggest that you use Excel to help create it.
For this example I have assumed that there are a maximum of 50 names on each worksheet, i.e. rows 3 to 52, adjust as required, but I suggest you have a larger range than is used, to allow for new names to be added later.
Having a larger range than necessary is not an issue for this formula

In an empty worksheet enter the names of your 11 worksheets in column A, starting at cell A1
In cell B1 enter this:
="OFFSET(" & A1 & "!$B$3,SUMPRODUCT((" &A1 & "!$B$3:$B$52=A1)*(ROW(" & A1 & "!$B$3:$B$52)-2))-1,1) & "
Drag this down to row 11, alongside the last worksheet name.
In Cell B11 remove the & at the end and the two spaces. It now ends: -2))-1,1)"
In cell B12 enter this formula:
=CONCATENATE(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11)
Now select cell B12 and Copy
Then select B13 and Paste Special ... Values
Now go into the formula bar for B13 and select all the text and right-click in the formula and Copy, then click the check mark to the left of the formula bar.

Go to the Summary worksheet and in the first cell in column B next to the first name, enter the formula bar, right-click and Paste.
Go to the front of the pasted text and enter an equals sign. Drag down as mentioned earlier.

Although this seems a long process most of the complication is in creating the formula because it has to be repeated 11 times. Each component of the formula is relatively simple:
=OFFSET(MyS1!$A$3,SUMPRODUCT((MyS1!$A$3:$A$52=A1)*(ROW(MyS1!$A$3:$A$52)-2))-1,1)
The above formula is for a worksheet named "MyS1"
The formula for one worksheet uses the OFFSET() function to return the value
OFFSET uses a row and column value as an offset from cell B3 (the first name)
The Column offset is 1 (the last 1 in this formula), so the value is returned from column C.
The row offset comes from the SUMPRODUCT() function which returns a row number using the ROW() function, when it finds a match. There are some adjustments for starting on row 3, notably the -2 in the formula
If the name is not found, the row offset is -1 and the value returned is "" from the blank row above the first row of data.
All the results from 11 of these formulas are joined together - typically 10 will be nothing "" and one will be the number next to the person's name.

Hope you can get this to work

Regards


Report •

#2
June 25, 2010 at 04:40:31
You could also use a UDF. This has been tested in a limited fashion and seems to work.

Option Explicit
Function absent(ByVal find_name As Range)
Dim sht As Integer, c As Variant
Application.volatile
'Loop through sheets
  For sht = 2 To Sheets.Count
'"Lookup" Value from argument cell in each sheet
    With Sheets(sht).Columns(2).Cells
      Set c = .Find(find_name)
'If found, return value from Column C
        If Not c Is Nothing Then
           absent = Sheets(sht).Cells(c.Row, 3)
        End If
    End With
  Next
End Function


Report •

#3
June 25, 2010 at 09:02:05
Hi,

If you prefer the User Defined Function route, here is a UDF that is similar to the standard VLOOKUP()

=multvlookup(Find, Range, Sheets, Offset)
Find: the value to search for or a cell reference containing the value,
Range: the range to be searched in - same on every page (the range is only the range to search in - it does not include the results - unlike Vlookup)
Sheets: the name of the first and last sheets to be searched, separated by :
Offset: a column offset - data is returned from this column on row matching Find value. 1 is the search column (like Vlookup)

This is what it looks like for the sample data
=multvlookup(A1,$B$3:$B$53,"MyS1:MyS11",2)

Option Explicit

Public Function MultVlookup( _
            FindThis As Variant, _
            LookIn As Range, _
            SheetRange As String, _
            OffsetColumn As Integer) _
        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 n As Integer

'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

'search range on each worksheet in array
blnFound = False
For n = 0 To UBound(SheetArray, 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
    MultVlookup = rngFind.Offset(0, OffsetColumn - 1)
    Else
    MultVlookup = "Not Found"
End If
End Function

Regards


Report •

Related Solutions

#4
July 4, 2010 at 07:36:10
Thanks for the help all. I overcame the problem by using OFFSET instead

Report •

#5
July 4, 2010 at 09:23:29
Hi,

Can you post your formula - it would be valuable to see another approach to this problem.

Regards


Report •

#6
July 4, 2010 at 09:41:30
I actually restructured the who sheet and now have no use for the summary so the INDEX formula was no longer needed. I just used a simple OFFSET to allow me to have a reference to specific cells regardless of how many columns were inserted

Report •

#7
September 3, 2010 at 13:00:05
Hi Humar,

I'd very much like to implement your solution - however I was wondering if you could help me with a problem. The value that I am searching for (which is a part code) may appear on multiple sheets (supplier price lists).

As I understand it, this function in its current form would return the first value that it found, much like a vlookup would.

Is there any way you could help me modify this function so that it would search all the worksheets for the value, and then return the lowest price for that value (which would be in the OFFSET column) and the name of the sheet that this was found in?

Thanks so much in advance for any help you can assist with!

Steve


Report •

#8
September 3, 2010 at 14:18:11
Hi,

I will have a look.

It won't be today - but I will try and get to it in the next two or three days.

Regards


Report •

#9
September 4, 2010 at 08:52:09
Hi,

Here is a UDF which will do a VLOOKUP type function on the same range on multiple worksheets, and return the minimum value associated with the lookup value.

So if your lookup value is "Grommet" and the word "Grommet" appears ten times in the specified range on multiple worksheets, it will return the lowest value in the specified offset column.

The UDF ignores empty cells in the offset column (which would return zero as the lowest value)

=MultVlookupMin(Find, Range, Sheets, Offset)
Find: the value to search for, or a cell reference containing the value,
Range: the range to be searched in - same on every page (the range is only the range to search in - it does not include the results - unlike Vlookup)
Sheets: the name of the first and last sheets to be searched, separated by : (colon)
Offset: a column offset - data is returned from this column, on the row matching the Find value.
1 is the search column (like Vlookup), so 2 is the column immediately to the right of the search range.

This is what it looks like for the sample data
=MultVlookupMin(A1,$B$3:$B$53,"MyS1:MyS11",2)

Here is the code for the UDF. This must go in a standard module.
You may wish to use a standard module in Personal.xls, so that the UDF is always available on your PC when you open Excel.
If you are going to send the workbook to others, then add the UDF to a module in the workbook.

Public Function MultVlookupMin( _
                    FindThis As Variant, _
                    LookIn As Range, _
                    SheetRange As String, _
                    OffsetColumn As Integer) _
                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 rngMin As Range
Dim intSheets As Integer
Dim strLastAddr As String
Dim n As Integer

'make this function volatile, as the addresses of the cells on
'all worksheets are not included in the function's parameters
'and the function will not recalculate if either the data in the
'search range changes, or the data in the found cell changes.
Application.Volatile

'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

'set minimum to nothing
Set rngMin = Nothing

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 - 1

'search range on each worksheet in array
For n = 0 To intSheets
    'first 'find' on this worksheet
    With Worksheets(SheetArray(n)).Range(LookIn.Address)
        Set rngFind = .Find(FindThis, LookIn:=xlValues, _
                        MatchCase:=False, LookAt:=xlWhole)
        'save first cell address - so we can stop find's wrap around
        strLastAddr = rngFind.Address
        'repeat find until no more matches on this worksheet
        Do Until rngFind Is Nothing
            'if first find, then save it as the minimum
            'but ignore if value cell is empty
            If rngMin Is Nothing And _
                        rngFind.Offset(0, OffsetColumn - 1).Value <> "" Then
                Set rngMin = rngFind.Offset(0, OffsetColumn - 1)
                Else
                'test if new value is smaller than saved value
                'but ignore if value cell is empty
                If rngFind.Offset(0, OffsetColumn - 1).Value _
                            < rngMin.Value And _
                            rngFind.Offset(0, OffsetColumn - 1).Value _
                            <> "" Then
                    Set rngMin = rngFind.Offset(0, OffsetColumn - 1)
                End If
            End If
            'set search range start to last found address
            Set rngFind = .Find( _
                        FindThis, _
                        LookIn:=xlValues, _
                        After:=rngFind, _
                        MatchCase:=False, _
                        LookAt:=xlWhole)
            'stop if Find is back at start
            If rngFind.Address = strLastAddr Then Set rngFind = Nothing
        Loop
    End With
Next n

'return value
If Not rngMin Is Nothing Then
    'return the minimum value
    MultVlookupMin = rngMin.Value
    Else
    MultVlookupMin = CVErr(xlErrNA)
End If
End Function

This has had only limited testing. If you get unexpected results please let me know - and include the data that caused the problem.

Regards


Report •

#10
September 10, 2010 at 11:17:43
Hi Humar;

I have a similar problem to Steve, I want to look a specific value on Multiple sheets and return a count of the number of times the offset value is equal to "W".

i.e. I want to lookup "Oakland" on the first sheet, the Offset value is a "W" so I want to increase the value of a counter by 1, same for the next sheet, etc.

I have modified your code as below, and the numWins Variable is being populated correctly, but can't seem to get it to return the actual value.

public function MultVlookup
...
...
Dim numWins

...
.....
blnFound = False
numWins = 0
For n = 0 To UBound(SheetArray, 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
If rngFind.Offset(0, OffsetColumn - 1) = "W" Then
numWins = numWins + 1
End If
End If
Next n

'return value
MultVlookup = numWins

End Function

Any help would be appreciated.

Thanks, Kyle


Report •

#11
September 10, 2010 at 13:57:52
✔ Best Answer
Hi,

The problem is that the MultVlookup function was designed to stop after finding the first match.

You correctly changed the logic to allow it to loop repeatedly, i.e. not stop after finding the first match, but the Find function does not stop when it has gone through the range specified - it just starts again, so you need to add a test to show that it has got back to where it started from - in practical terms if it finds a matching cell that is the one it found first of all.
Secondly, Find always starts with the cell after the first cell in the range passed to it. The accepted way to repeat Finds with each find starting after the last cell matched is to use FindNext - but this does not work when called from a UDF. As a result you have to use repeated Finds and manage the starting addresses each time.

In your case because you want multiple 'finds', the code I proposed in VlookupMin is more appropriate as a starting point.
You will see that this saves an address and uses it as a test in the Do Until loop, the loop stopping when the address found matches the saved address of the first matching cell.

I made a few mods to the VlookupMin and this is what I came up with:

Public Function MultVlookupW( _
                    FindThis As Variant, _
                    LookIn As Range, _
                    SheetRange As String, _
                    OffsetColumn As Integer) _
                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 numWins As Integer
Dim intSheets As Integer
Dim strLastAddr As String
Dim n As Integer

'make this function volatile, as the addresses of the cells on
'all worksheets are not included in the function's parameters
'and the function will not recalculate if either the data in the
'search range changes, or the data in the found cell changes.
Application.Volatile

'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 - 1

'initialize numWins
numWins = 0

'search range on each worksheet in array
For n = 0 To intSheets
    'first 'find' on this worksheet
    With Worksheets(SheetArray(n)).Range(LookIn.Address)
        Set rngFind = .Find(FindThis, LookIn:=xlValues, _
                        MatchCase:=False, LookAt:=xlWhole)
        If Not rngFind Is Nothing Then
            'save first matching cell address -
            'so we can stop find's wrap around
            strLastAddr = rngFind.Address
            'and increment numWins if offset cell value is "W"
            If rngFind.Offset(0, OffsetColumn - 1).Value = "W" Then
                numWins = numWins + 1
            End If
        End If
        'repeat find until no more matches on this worksheet
        Do Until rngFind Is Nothing
            'set search range start to last found address
            Set rngFind = .Find( _
                        FindThis, _
                        LookIn:=xlValues, _
                        After:=rngFind, _
                        MatchCase:=False, _
                        LookAt:=xlWhole)
            'stop if Find is back at start
            If rngFind.Address = strLastAddr Then
                Set rngFind = Nothing
                Else
                'increment numWins if offset cell value is "W"
                If rngFind.Offset(0, OffsetColumn - 1).Value = "W" Then
                    numWins = numWins + 1
                End If
            End If
        Loop
    End With
Next n

'return value
MultVlookupW = numWins
End Function

I called it MultVlookupW only because I already have a MultVlookup and can't have two in the same copy of Excel.

Hope this does what you were looking for.

Regards


Report •

#12
September 10, 2010 at 14:26:38
Thanks very much, works perfectly.

Great UDF.

Kyle


Report •

#13
September 10, 2010 at 14:34:10
You're welcome.

Thanks for letting me know it worked.

Regards

Humar


Report •

#14
November 17, 2010 at 06:19:48
Hummar,

quick question, if you change the source of information the MultVlookup won't refresh automatically? do you know how to make it happend?

thanks

Marcelo


Report •


Ask Question