Excel Problem on WINDOWS

[AndreanMiky]

Dear Folks,

Please Need Your Help and solve Excel problem on Windows. if i use excel on MAC no problem and can work fine.

I displayed  2 Screenshoot which will be compared using the formula in Column A (=CONCATENATE(D2;F2)), and for the results using the formula in column M ("=XLOOKUP(A2;Sheet1!A:A;Sheet1!B:B) "). 

A. Excel MAC

B. Excel WIN

[#2]

can you please attach the same file here, look to me issue with referencing formula range

Regards, Faraz Shaikh, Microsoft MVP

[#3]

Dear @Faraz Shaikh Attached

[#4 · Accepted solution]

@AndreanMiky When you break down the text strings to their individual character codes, you'll see that the strings in Sheet1 contain regular spaces (character code 32) whereas the strings on Sheet2 contain non-breaking spaces (code 160 on the PC, code 202 on the Mac).

Now the odd thing is that Excel for Mac considers code 32 to be equal to code 202. Hence, XLOOKUP finds a match and returns a value. Excel for PC, however, does not consider code 32 to be equal to code 160, and XLOOKUP correctly returns #N/A. Can't really explain why Excel is behaving like this on the Mac.

[#5]

This is great information and new to me.. thanks for sharing it !!

[#6]

@AndreanMiky

So, workaround could be

=XLOOKUP(
    SUBSTITUTE(A2, CHAR(160), " "),
    Sheet1!A:A,
    Sheet1!B:B
)

[#7]

thank you Pak