XLOOKUP returning #N/A error

Dear ExcelForum users,

I'm encountering a problem with XLOOKUP in my data where some values match while others do not, even though those values exist in the lookup array. I have attached a sample Excel file for reference.

Specifically, I am using the value in cell C4 from my source file to perform an XLOOKUP on another Excel file to retrieve corresponding values. However, XLOOKUP returns an #N/A error. When I press F2 and then Enter on cell C4, XLOOKUP works correctly. The issue is that I cannot identify any difference in the value in cell C4 before and after pressing F2 and Enter.

I have also tried using the VALUE formula on cell C4, but it doesn't resolve the issue. Applying the NUMBERVALUE formula or combining VALUE with the CLEAN formula on cell C4 makes XLOOKUP work properly.

My questions are:

What is the actual difference between the NUMBERVALUE formula and the VALUE formula?
Why does XLOOKUP fail when there appears to be no visible difference in cell C4 before and after pressing F2 and Enter?

Thanks