XLookup Not Finding a Match (when I see that the data is there)

[slbrick13]

This is about XLookup not working for me.

I'm using Microsoft 365 (Excel) in Windows 10 Enterprise 

I am using XLookup (for the first time) in VBA for an Excel Workbook that I am modifying for work.  There is no "failure".  However, the lookup is not finding a match.  I know that (visually) it looks like a match but XLookup does not think that the search argument and the target data match. 

I am not permitted to copy the workbook here (work restrictions) so I will try to give a detailed explanation here.

1. The search argument is a value in (what I will call here) Worksheet1 (ws1). 

2. The target array/range is in Worksheet2 (ws2)

3. The result array/range is in Worksheet2 (ws2)

4. The search argument is 20210800703.   I'm not sure what the datatype of this search argument is.  But I define a variable as a string and load the variable with the data from ws1.   That seems to work.

5. The target array/range does, indeed, have a cell with value 20210800703 in it.  And the column is defined as containing text data

Here is the excerpt of code:

(I am not putting the Dim statements here ... but they are in my function)

(and the indentations are not happening in this editor ... sorry about that)

set wb = This Workbook
set ws1 = wb.Worksheets("InpData")
set ws2 = wb.Worksheets("ProjectTable")

with ws2
.Select
projTableLastRow = ws2.Range("A1000000").End(xlUp).Row
end with

set targetRange = ws2.Range("A2:A" & projTableLastRow)
set resultRange = ws2.Range("B2:B" & projTableLastRow)

ws1.Select
shName = ws1.Name

projIdCol = GetColNbr(shName, "Project ID")
categoryCol = GetColNbr(shName, "Category")
projNameCol = GetColNbr(shName, "Project Name")

For ix = 2 to lastRow

'pull project id from input data worksheet
projId = Cells(ix, projIdCol).Value

Cells(ix, projNameCol) = Application.WorksheetFunction.XLookup(projId, _
                         targetRange, _
                         resultRange, _
                         "Not Found")

Next ix

All rows in ws1 are getting "Not Found" in the Project Name column.  But the data is there in the ProjectTable tab.

So ... I'm thinking that there is an issue with the datatypes of the search argument and the target array.

Maybe.  

So if anybody out there has an idea why XLookup is not finding a match ... I would love to hear it.  Thanks.

[#2]

I agree you are probably having a problem with 1 being a number and the other being text. try force formatting of both sets to be text in the code. OR better yet, dump the worksheet function and use VBA. you are already in VBA and I believe I've seen time tests showing that this type of search is much more efficiently done using a loop in VBA than to use a built in like that. Just loop through the array, check and exit when found.

[#3]

Thanks, so much, for the confirmation. I will try to do the loop using VBA. Great idea.