I'm trying to compare data recently pulled from a database into an excel spreadsheet to one pulled about 2 months ago. When I use Xlookup to find my array in the current workbook I get one array based on the Key I'm looking for. However, when I use Xlookup to find the same key in the older spreadsheet data, I get a completely different key's data.
I've trying going from spreadsheet a-b and b-a. Same result. I've truncated the array to a single cell's data. I've written VBA code for the worksheet function. Same results for all.
=XLOOKUP(EO3,BK:BK,AL:BJ,"",0,1)
=XLOOKUP($EO3,'[01-29-2024.xlsm]Step'!$BK:$BK,$AL:$BJ,"",0,1)
Result = Application.WorksheetFunction.XLookup([EO3], [BK:BK], [AL:BJ], "", 0, 1)
Test = Application.WorksheetFunction.XLookup([EO3], Workbooks("01-29-2024.xlsm").Sheets("Step").Range("BL:$BL"), [$AL:$BJ], "", 0, 1)
Initially I thought I saw that excel was truncating my Key from:123400000000-000000-002 to: 1234000000000-000000-2.
I spent some time trying to format the key differently, but I have not been able to correct the issue. I verified Key is text using
=ISTEXT(EO3)
result is TRUE.
Sorry for the redacted image/key. Customer data.