Multiple VLookup Columns

Question

I use the VLookup function, which searches for several values ​​that are present in a column. This works very well, but takes a lot of time since I have 100,000 rows in an Excel worksheet.

Is there a way to speed up this code?

The code basically looks for a specific value in the column and gets the offset. The difference between a simple VLookup and the fact that if there are multiple lines with the same search value, 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 

Answer 1

This is about 20-30 times faster than a simple cycle (it is checked for a column of 20 thousand, with 3 matches with the desired value).

 '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 

Answer 2

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

Answer 3

You can try doing Range.Find to see if a value exists at all in the search column before continuing. You look at each item in the search column only to find it is not there. If it were me, I would do Range.find to see if there is a lookup value in lookup_column. If so, you can count to find out how many occurrences exist ... if there is only one occurrence, use the plain old VLookup ... and go back to your process if there are several cases ..... may work. ... of course, if Find failed, exit the function.

Another option is to load lookup_column into any array ... and process the array, not the .mn range, which can sometimes help.

Answer 4

Summary: Match the values ​​and do a vlookup for this new value.

For me, I needed a formula, not a function to search by 2 values. vlookup can only work on one value from what I saw, so my solution was to combine the two values ​​for one primary key.

On the Raw Data tab, I added a Lookup column, which simply concatenated the identifier column with the Timestamp columns that I had.

Then on my comparison tab I had

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

What took the identifier column associated with my search date in $F$1 and vlookup 'in my data tab (Historical data).