通过Excel VBA将vlookup转换为超过255个字符

Question

我正在寻找在Excel VBA中拥有超过255个字符的反向vlookup。 这是我从这个网站上获取的基于公式的公式。

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

我尝试在 VBA 中转换它。下面是示例代码

Sub test()
'concat
Range("i1") = WorksheetFunction.TextJoin(" ", True, Range("g1:h1"))
'lookup
Sal1 = Application.WorksheetFunction.Index(Sheets("sheet1").Range("a1:a2"), Application.WorksheetFunction.Match(True, Application.WorksheetFunction.Index(Sheets("sheet1").Range("i1:i1") = Range("i1").Value, 0), 0))
'=INDEX($W$3:$W$162,MATCH(TRUE,INDEX($W$3:$W$162=U3,0),0))

End Sub

它运行良好，但当我将range("i1:i1")更改为range("i1:i2")时却没有

Answer 1

我不确定工作表公式的作用=INDEX(F2:F11,MATCH(A2,D2:D11,FALSE))没有做什么。

这部分Index(Sheets("sheet1").Range("i1:i2") = Range("i1").Value, 0)将二维数组与单个值进行比较，这应该会导致类型不匹配错误。每当引用多单元格区域的 Value 属性(Value 是此上下文中的默认属性)时，即使范围是单列或单行，也会得到二维数组。

你可以用Application.WorksheetFunction.Transpose(Range("D1:D10"))解决这个问题，把它变成一维数组，但我仍然不认为你可以将一维数组与单个值进行比较，并让它返回适合传入 INDEX 的东西。

您可以使用 VBA 来创建真和假数组，但如果你要去麻烦，你应该使用 VBA 来完成整个事情并放弃 WorksheetFunction 方法。

在将单个单元格与单个单元格进行比较时，我无法像您所说的那样让它工作。

这是重现公式的一种方法

Public Sub test()

Dim rFound As Range

'find A2 in D
Set rFound = Sheet1.Range("D1:D10").Find(Sheet1.Range("A2").Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then
MsgBox rFound.Offset(0, 2).Value 'read column f - same position as d
End If

End Sub

如果这个更简单的公式有效并且您想使用WorksheetFunction，它将看起来像这样

Public Sub test2()

Dim wf As WorksheetFunction

Set wf = Application.WorksheetFunction

MsgBox wf.Index(Sheet1.Range("F2:F11"), wf.Match(Sheet1.Range("A2").Value, Sheet1.Range("D2:D11"), False))

End Sub

Answer 2

Function betterSearch(searchCell, A As Range, B As Range)
For Each cell In A
If cell.Value = searchCell Then
betterSearch = B.Cells(cell.Row, 1)
Exit For
End If
betterSearch = "Not found"
Next
End Function

我从上面的链接中找到了这段代码，它对我当前的搜索很有用。下面的例子我试图获得价值。

请将 A 列和 B 列的第 1 行到 5 视为空，因为我的表总是从第 6 行开始

列

行	A	B 列
6	54	安培
7	55	字节
8	56	分

通过 VBA 表示匹配项

我想知道是否有任何转换此公式的可能性。

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

因此，标题中的"反向 VLookUp">只是意味着通过 VBA 表达(单个)公式结果(顺便说一句，我坚持使用 OP 中的单元格引用，因为您在评论中提到了不同的范围地址)。

这可以通过简单的评估来完成，给你一个开始的想法：

'0) define formula string
Dim BaseFormula As String
BaseFormula = "=INDEX($F$2:$F$10,MATCH(TRUE,INDEX($D$2:$D$10=$A2,0),0))"
'1) display single result in VB Editor's immediate
Dim result
result = Evaluate(BaseFormula)
Debug.Print IIf(IsError(result), "Not found!", result)

另一方面，您似乎有意扩展搜索字符串范围 从A2到更多输入(例如，直到单元格A4)。基本公式不会返回具有此公式的结果数组， 但是您可以通过在例如 3 行上复制开始公式来按如下方式进行(请注意相对地址...=$A2以允许在下一行中增加行)：

'0) define formula string
Dim BaseFormula As String
BaseFormula = "=INDEX($F$2:$F$10,MATCH(TRUE,INDEX($D$2:$D$10=$A1,0),0))"
'2) write result(s) to any (starting) target cell
'a)Enter formulae extending search cells over e.g. 3 rows (i.e. from $A2 to $A4)
Sheet3.Range("H2").Resize(3).Formula2 = BaseFormula
'b) optional overwriting all formulae, if you prefer values instead
'Sheet3.Range("H2").Resize(3).Value = Tabelle3.Range("G14").Resize(3).Value

当然，您可以通过任何动态替换来修改公式字符串(例如，通过应用于某些预定义范围的属性.Address(True,True,External:=True)以获得本例中的绝对完全限定引用)。

对所用公式的一些解释

引用链接中的公式

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

描述一种在直接匹配超过255 个字符的字符串时避免不可避免的#NA 错误的方法。

基本上，它是"在 D2：D10 中查找 A2 并从 F2：F10 返回结果">，类似于在这种情况下的(失败的)直接方法：

=INDEX(F2:F11,MATCH(A2,D2:D11,FALSE))

诀窍是提供一组True|False元素(INDEX(D2:D10=A2,0)) 最终可以匹配，而不会出现问题True。

通过 Excel/MS 365 提供全功率

但是，如果您处理Excel/MS 365，您甚至可以使用以下更简单的功能 并从所谓的泄漏范围内动态显示结果中获益。 这意味着匹配不仅可以基于一个搜索字符串，还可以基于多个搜索字符串(例如A1:A2)， 什么似乎可以解决您的其他问题(参见OP中的最后一句话)以扩展搜索范围。

=XLOOKUP(A1:A2,D2:D10,F2:F10,"Not found")