XLookup function won't detect an exact match until I place the cell in edit mode and then exit-CodePudding

I have what I think is a strange error. I am using the XLookup function in excel using Excel 365. I use a VBA script to create an ID number for a row of data and then I place that ID number into a cell. Everything appears to be normal. However, if I use the XLookup function with Exact Match to locate that ID number and give me some value in a column next to it, it fails to find it and I get an N/A.

However, if I manually locate that exact ID number and place the cell in edit mode and then exit the cell without taking any other step to alter the data in any way, the XLookup function suddenly returns the correct result.

If I leave the cell in it's initial state but use the Match Mode "-1" to get an exact match or next smaller, it returns the correct result. I don't want to leave it in that mode, because my final function will actually be two nested Xlookup functions that check one table for the ID and if not there, looks at an alternate table, so I don't want the first function to return a "next smaller" result.

When I generate the ID number in the VBA script, I generate it as the type "Variant" because the ID number is a decimal, such as 210.1. I then simply set that cell's Value to the variant ID number.

Here is a how I am placing the data into the cell:

Dim myID As Variant
Dim myCell As Variant
Dim myRange As Range

Set myRange = Range(someNamedRange)

myID = GenerateNextID 'This generates a variant in the form of something like 201.1

For Each myCell In myRange

    myCell.Value = myID
    myID = myID   0.1

Next myCell

Excel seems to recognize it as a number; at least I seem to be able to do math functions on the ID number and get correct results.

I've tried putting the lookup ID number in quotes to see if looking it up as text instead of a number produced a different result, but it didn't change my result.

Anyone have an idea as to why I'm getting this result? A way to resolve it without using a different Match Mode?

Any help is appreciated.

*Edited to add code sample.

CodePudding user response：

You have a floating point precision error. The value in E11 is 211.39999999999998 rather than 211.4. Excel corrects the error when you re-enter the cell's value.

Write the value using a line like:

myID = Round(myID   0.1, 1)