VLookup multiple columns

Question

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

If you sort your lookup column then you can exit the loop as soon as you have at least one found value and the next row is a non-match. Are you calling this from a worksheet formula, or from VBA? — Tim Williams, Sep 20 '13 at 22:37

Tim Williams · Accepted Answer · 2018-04-06T02:44:06.050

5

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

edited Apr 06 '18 at 02:44

answered Sep 20 '13 at 23:57

Tim Williams

154,628
8
97
125

hi Tim, thank you very very much! it has reduced the time from 1 minute to 1 sec!!! – codemacha Sep 23 '13 at 13:48
This has no comments! Can someone explain what "val" and "col" are? – Shawn V. Wilson Apr 05 '18 at 21:58

score 2 · Answer 2 · answered Sep 20 '13 at 20:41

2

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

answered Sep 20 '13 at 20:41

pnuts

58,317
11
87
139

hi, the only problem I have is that I need it to return all the rows where it matches. – codemacha Sep 23 '13 at 13:11

score 0 · Answer 3 · answered Sep 20 '13 at 20:40

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.

score 0 · Answer 4 · answered Oct 23 '17 at 14:23

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)

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

VLookup multiple columns

4 Answers4

Linked