SOLVED

# Excel Problem on WINDOWS

Copper Contributor

# Excel Problem on WINDOWS

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

6 Replies

# Re: Excel Problem on WINDOWS

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

Regards, Faraz Shaikh, Microsoft MVP

# Re: Excel Problem on WINDOWS

Dear @Faraz Shaikh Attached

best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Excel Problem on WINDOWS

@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.

# Re: Excel Problem on WINDOWS

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

# Re: Excel Problem on WINDOWS

So, workaround could be

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

# Re: Excel Problem on WINDOWS

thank you Pak
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Excel Problem on WINDOWS

@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.