CODEWITHSUNDEEP
vbaexcelvlookup
codemacha
codemacha

Reputation: 77

VLookup multiple columns

I am using VLookup function which looks up multiple values which are present in the column. This works very well but just takes a lot of time as I have 100,000 rows in the Excel sheet.

Is there any way to quicken this code?

The code basically looks up a particular value in a column and gets the offset. The difference between simple VLookup and this is that in case there are multiple rows with the same lookup value then it gets all the elements.

   Function VLookupAll(ByVal lookup_value As String, _
                ByVal lookup_column As Range, _
                ByVal return_value_column As Long, _
                Optional seperator As String = ", ") As String

 Dim i As Long
 Dim result As String

For i = 1 To lookup_column.Rows.Count
If Len(lookup_column(i, 1).Text) <> 0 Then
    If lookup_column(i, 1).Text = lookup_value Then
        result = result & (lookup_column(i).Offset(0, return_value_column).Text & seperator)
    End If
End If
Next

If Len(result) <> 0 Then
result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

Upvotes: 2

Views: 19689

Answers (4)

Tim Williams
Tim Williams

Reputation: 166306

This is about 20-30x faster than a simple loop (tested over a column of 20k values, with 3 matches to the value being searched).

'rng: a single-column range to search for matches
'val: the value to match on
'col: offset from match in rng to concatenate values from (similar
'       to the matching VLOOKUP argument)
Function MultiLookup(rng As Range, val As String, col As Long)

    Dim i As Long, v, s
    Dim r As Long

    r = rng.Cells.Count
    v = Application.Match(val, rng, 0)
    s = ""
    Do While Not IsError(v)
        s = s & IIf(s <> "", ",", "") & rng.Cells(v).Offset(0, col - 1).Value
        r = r - v
        Set rng = rng.Offset(v, 0).Resize(r, 1)
        v = Application.Match(val, rng, 0)
    Loop
    MultiLookup = s

End Function

Upvotes: 6

Zorgarath
Zorgarath

Reputation: 1139

Summary: Concate the values and do a vlookup on that new value.

For me I needed to have a formula and not a function to look up by 2 values. VLOOKUP could only work by a single value from what I've seen, so my solution was to concatenate the 2 values for a single primary key.

In my raw data tab I added a column called Lookup that simply concatenated the ID column with the Timestamp columns I had.

Then in my comparison tab I had 

=VLOOKUP(CONCATENATE(A4, $F$1),'Historical Data'!$A:$G,3,FALSE)

Reporting Tab

Which took the ID column, concatenated with my lookup date at $F$1, and vlookup'ed into my data tab (Historical Data). Historical Data tab

Upvotes: 0

pnuts
pnuts

Reputation: 59475

http://www.excelhero.com/blog/2011/03/the-imposing-index.html says "Excel INDEX MATCH is significantly quicker than VLOOKUP"

Upvotes: 2

smackenzie
smackenzie

Reputation: 3022

You could try doing a Range.Find to see if the value exists at all in lookup column before proceeding. You are looping through every item in lookup column only to find it isn't there. If it were me, I would do a Range.find to see if lookup value is in lookup_column. If it is then you could do a countif to see how many occurrences there are...if there is only one occurrence, use plain old VLookup...and only fall back into your process if there is more than one occurrence.....may work....of course if Find fails, bail out of the function.

Another option is to load the lookup_column into any array...and process the array rather than the range.mnthat can sometimes help.

Upvotes: 0

Related Questions