Multiple Vlookup function - Question

[mattkorguk]

Hi,
I've been trying to get the below to work on a list 42000+ id's and it does work fine on the first 156 lines, no problem, returning as expected. When I try and copy the formula to line 157 it just freezes...
The below was taken from MrExcel site I believe...

'Vlookup Multiple Criteria
'Returns all values matching vlookup criteria, separated by a comma
'=mvlookup(a1,sheet2!$a$1:$c$43000,4)
Function mvlookup(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant
Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim myStr As String
Dim initTableCols As Long
Dim i As Long
Dim ubound_myRes As Long
Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0
If initTable Is Nothing Then
mvlookup = CVErr(xlErrRef)
Exit Function
End If
initTableCols = initTable.Columns.Count
i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)
If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0).Resize(initTable.Rows.Count - myRowMatch, initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop
If i = 0 Then
mvlookup = CVErr(xlErrNA)
Exit Function
End If
myStr = ""
For i = LBound(myRes) To UBound(myRes)
myStr = myStr & ", " & myRes(i)
Next i
mvlookup = Mid(myStr, 3)
End Function

If you break the code it seems like it's stuck in a loop here;

ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do

Any ideas?!? Thanks.

 

[Ginny2222]

Hi Matt,
I have reproduced the above function and it seems to work perfectly ... but ... it does appear to struggle on large amounts of data. Maybe it just has too much work to do!!!

I set up 4 columns with 43000 rows. I then added a column of search items in another part of the worksheet and used the formula. When I filled down with the formula, there was no problem with a few hundred items, but when I auto-filled down, the formula filled into the cells all the way down the sheet, but the results froze. When I ended the process the formula had filled in values as far as about 3,500, with #N/A in all the rest of the cells!! I wonder if it was left long enough would it complete the function???

rgs
Ginny