Why can't my Application.XLOOKUP match numeric values?

Question

My Excel VBA functions works on text values but when the lookup value contains numbers only, the XLOOKUP can't find a match.

Range(SelectedCell) = Application.XLOOKUP("*" & Range(LookupCell) & "*", Worksheets("Stuklijst").Range(Mod_SelectColumns.StuklijstRefRange.Address), Worksheets("Stuklijst").Range(Mod_SelectColumns.StuklijstMPNCol.Address), "NOT FOUND", 2)

I tried adding a format function to the Lookup Value and Search Array and I tried storing the cell data as a Number, Text and General. Next to that I tried adding a Format function to the lookup value and lookup array part of the Xlookup function.

When I execute an XLOOKUP manually on the Cell values in one of the sheets, the function works as expected.

What am I doing wrong and how can I fix it?

Edit: I replicated my issue with some generated data

1. Original data before executing the XLOOKUP the Articenumber column is empty. The numeric values are in the General Number format, such are the other values.

The LookupSheet. Just as the picture above the datatype of the numeric and other Values are General.

As you can the XLOOKUP function can't find the numeric values and results in "NOT FOUND" (Yes I have checked for spaces at the end of value). The code I use to find the Articlenumbers in the LookupSheet (the button calls the subroutine):

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)

Range(selectedCell) = Application.XLookup("*" & Range(lookupCell) & "*", 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

Answer 1

Given your data, it is not apparent to me why you need wild card lookups.

Also XLOOKUP can return an array, so there is no need to process each cell individually.

In general, VBA routines run faster if one minimizes the access to the worksheet cells, and does the processing solely in VBA. In this particular instance, since XLOOKUP will return an array, I'm not certain which method would be faster, but I have shown the "process in VBA" routine.

For a non-wild card XLOOKUP you could adapt the following:

Option Explicit
Sub due()
    Dim vLookupVal As Variant
    Dim vLookupArr As Variant
    Dim vLookupRes As Variant
    Dim vArtNum As Variant
    Dim v As Variant
    Dim rArtNum As Range
    
With ThisWorkbook.Worksheets("Sheet1")
    vLookupVal = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    vLookupArr = Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
    vLookupRes = Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp))
    Set rArtNum = .Cells(1, 2).Resize(rowsize:=UBound(vLookupVal))
End With

     rArtNum = Application.XLookup(vLookupVal, vLookupArr, vLookupRes, "NOT FOUND")
    
End Sub

If you require the wildcard for some reason not apparent in your example, then modify

• the vLookupVal array to be text and include the wildcards
• the vLookupArr array to be text
• the XLOOKUP function to do a wild card search

Option Explicit
Sub due()
    Dim vLookupVal As Variant
    Dim vLookupArr As Variant
    Dim vLookupRes As Variant
    Dim vArtNum As Variant
    Dim I As Long
    Dim rArtNum As Range
    
With ThisWorkbook.Worksheets("Sheet1")
    vLookupVal = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    vLookupArr = Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
    vLookupRes = Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp))
    Set rArtNum = .Cells(1, 2).Resize(rowsize:=UBound(vLookupVal))
End With

    For I = 1 To UBound(vLookupVal, 1)
        vLookupVal(I, 1) = "*" & CStr(vLookupVal(I, 1)) & "*"
        vLookupArr(I, 1) = CStr(vLookupArr(I, 1))
    Next I

     rArtNum = Application.XLookup(vLookupVal, vLookupArr, vLookupRes, "NOT FOUND", 2)
    
End Sub