为何Excel VBA中Application.XLOOKUP无法匹配数值型数据？

VBA中使用Application.XLOOKUP带通配符匹配时，纯数字查找值无法匹配的核心原因是类型不兼容：

• 通配符*会把查找值强制转换为文本类型；
• 查找区域中的纯数字仍为数值类型；
• 手动在工作表中使用XLOOKUP时，Excel会自动兼容文本与数值的类型匹配，但VBA的Application.XLOOKUP不会自动执行这个转换，导致类型不匹配无法找到结果。

将查找区域的所有值强制转换为文本类型，确保与带通配符的文本查找值类型一致。可以通过Application.Text函数批量转换查找区域的值为文本格式。

Sub XLOOKUPTEST()

Dim rangeMpn As Range
Dim rangeArtikel As Range
Dim rangeLookupArtikel As Range
Dim rangeLookupMPN As Range
Dim selectedCell As String
Dim lookupCell As String
Dim rowCount As Integer
Dim i As Integer

Set rangeMpn = Worksheets("Resultsheet").Range("B2:B15")
Set rangeArtikel = Worksheets("Resultsheet").Range("C2:C15")
Set rangeLookupArtikel = Range("A2:A8")
Set rangeLookupMPN = Range("B2:B8")
rowCount = 14

For i = 2 To rowCount
selectedCell = "C" & CStr(i)
lookupCell = "B" & CStr(i)

' 核心修改：用Application.Text将查找区域转为文本格式，同时用CStr确保查找值为文本
Range(selectedCell) = Application.XLookup("*" & CStr(Range(lookupCell).Value) & "*", _
    Application.Text(Worksheets("LookupSheet").Range("A2:A9"), "@"), _
    Worksheets("LookupSheet").Range("B2:B9"), _
    "NOT FOUND", 2)

On Error Resume Next
If Range(selectedCell) = "" Then
Range(selectedCell) = "FOUND BUT NULL"
End If

Next i

End Sub

如果不想用Application.Text，也可以通过遍历查找区域的数组，将每个数值转换为文本后再进行查找，效果一致：

Dim lookupArray As Variant
lookupArray = Worksheets("LookupSheet").Range("A2:A9").Value
' 遍历数组转换为文本
For i = LBound(lookupArray) To UBound(lookupArray)
    lookupArray(i, 1) = CStr(lookupArray(i, 1))
Next i
' 使用转换后的数组进行查找
Range(selectedCell) = Application.XLookup("*" & CStr(Range(lookupCell).Value) & "*", _
    lookupArray, _
    Worksheets("LookupSheet").Range("B2:B9").Value, _
    "NOT FOUND", 2)

内容的提问来源于stack exchange，提问作者DanielvL