Xlookup not working for all list items

[Novice Excel User]

Why is this formula recognizing some of the items entered into C8 in the spreadsheet (AD102698), to return the data I'm looking for but not all?
Formula: =XLOOKUP(C8&" ",'AD102698'!$A$5:$A$3533,'AD102698'!$M$5:$M$3533,0,0,1)
Two worksheets: Calculator, where the formula is going and AD102698 where the data table is stored.
C8 of the Calculator worksheet is a manually entered field to enter a unique code into, which could be made up of a combo of numbers and text (examples: 61507, 08OAD, C08OAD, 6A011, EL004, 03CCB, 03CCBO).
Of the code examples, the formula provided above will return the information I need for the following codes: 61507, 08OAD, 6A011, EL004, 03CCB. It will not return the data for these codes: C08OAD, 03CCBO.

There are thousands of codes in the table and I need to make sure that the formula in D8 of the Calculator worksheet will recognize the job code entered into C8 in the Calculator worksheet, in column A of the the AD102698 worksheet, will look across the data in that row to return the information in column M of that same row, back to D8 of the Calculator worksheet.

The above noted formula seems to work for more of the codes but noticing there are lots that are not being recognized, despite typing them correctly or copy/pasting from AD102698 to C8 of the Calculator worksheet.

Any thoughts?

 

[Snakehips]

@Novice Excel User
The C8&" " element of your formula seems bit odd to me.
If you are dealing with data that might have leading or trailing spaces then maybe try ....

=XLOOKUP(C8,TRIM('AD102698'!$A$5:$A$3533),'AD102698'!$M$5:$M$3533,0,0,1)

HTH

 

[Novice Excel User]

@Novice Excel User
The C8&" " element of your formula seems bit odd to me.
If you are dealing with data that might have leading or trailing spaces then maybe try ....

=XLOOKUP(C8,TRIM('AD102698'!$A$5:$A$3533),'AD102698'!$M$5:$M$3533,0,0,1)

HTH

Thank you, Snakehips! That seems to have fixed my problem....and without having to provide an extract no less. I thought my details were pretty thorough; enough to not have to provide an extract as the information in the document I'm working on is confidential and cannot be shared. So I do apologies if my information seems vague but the skilled folks on here have been awesome and helping me out regardless.