vba Vlookup 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18925313/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VLookup multiple columns
提问by codemacha
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.
我正在使用 VLookup 函数来查找列中存在的多个值。这很有效，但只需要很长时间，因为我在 Excel 工作表中有 100,000 行。
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.
该代码基本上查找列中的特定值并获取偏移量。简单的 VLookup 和 this 之间的区别在于，如果有多行具有相同的查找值，那么它会获取所有元素。
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
回答by Tim Williams
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).
这比简单循环（在 20k 个值的列上进行测试，与被搜索的值匹配 3 个）大约快 20-30 倍。
'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
回答by pnuts
http://www.excelhero.com/blog/2011/03/the-imposing-index.htmlsays "Excel INDEX MATCH is significantly quicker than VLOOKUP"
http://www.excelhero.com/blog/2011/03/the-imposing-index.html说“Excel INDEX MATCH比 VLOOKUP 快得多”
回答by smackenzie
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.
在继续之前，您可以尝试执行 Range.Find 以查看查找列中是否存在该值。您正在遍历查找列中的每个项目，却发现它不存在。如果是我，我会做一个 Range.find 来查看查找值是否在 lookup_column 中。如果是，那么你可以做一个 countif 来看看有多少次出现......如果只有一次出现，请使用普通的旧 VLookup......如果出现不止一次，则只回退到你的过程...... ..可能会工作....当然，如果 Find 失败，请退出该功能。
Another option is to load the lookup_column into any array...and process the array rather than the range.mnthat can sometimes help.
另一种选择是将 lookup_column 加载到任何数组中...并处理数组而不是 range.mn 有时可以提供帮助。
回答by Zorgarath
Summary:Concate the values and do a vlookupon that new value.
摘要：连接值并对vlookup新值执行 a 。
For me I needed to have a formula and not a function to look up by 2 values. VLOOKUPcould 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.
对我来说，我需要一个公式而不是一个函数来查找 2 个值。VLOOKUP只能通过我所看到的单个值工作，所以我的解决方案是连接单个主键的 2 个值。
In my raw data tab I added a column called Lookup that simply concatenated the ID column with the Timestamp columns I had.
在我的原始数据选项卡中，我添加了一个名为 Lookup 的列，它只是将 ID 列与我拥有的时间戳列连接起来。
Then in my comparison tab I had
然后在我的比较选项卡中我有
=VLOOKUP(CONCATENATE(A4, $F),'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).
其中取了 ID 列，与我在 的查找日期连接$F$1，并vlookup输入到我的数据选项卡（历史数据）中。
