Used xlookup and came back with result #n/a although text fields are the same

[tanacal]

Hi, can anyone give me some help please?

I have the following downloaded data from a financial system:

When I tried to pull in the number column from the matched description in another table, I received #n/a:

I also realized the text fields are not equal although they are the exact same text.

I have checked the length of both text and length is the same 51 characters.

Can you please help me? Thanks for your help in advance.

Anna

[#2]

@tanacalIs it possible you have one or more trailing spaces in the target string in column C or the lookup value in Column Q? Try using TRIM function to correct for this.

[#3]

@Steve1330 

Thanks for your help. Appreciated. 

I have tried to use TRIM function to make the correct, but it was not working.

I think you are right on trailing spaces in the target string in column C. I just tried to use =SUBSTITUTE(C2,CHAR(160)," ") to fix it and it came out some improvements. Below is the picture now (Column U is result of xlookup after replacing the Column C with formula result):

Do you think if you can provide me suggestion for the last two #N/As? Thanks,

[#4]

@TANACAL125 To find which characters are causing the problem, you can perhaps use the formula demonstrated in the attached workbook. It lists out each and every character in your text and puts the corresponding code next to it. That way, you can visualise the white space in your text and use TRIM, CLEAN and/or SUBSTITUTE to get rid of them.

[#5]

@Riny_van_Eekelen 

Thanks, Riny. I think this is exactly what I was looking for. By the way, once I paste the text, how do I find out which character to fix? Is there any reference for code? I am sorry I am new to this and need your further help please. Thanks, appreciated. 

[#6]

@TANACAL125 Sorry, I wasn't clear. Copy columns B, C and D and paste them somewhere towards the right. Then enter your other text (the one that looks the same but doesn't match). Now compare the two code columns to see what the character codes don't match.

[#7]

@Riny_van_Eekelen Thanks so much for your explanation. Appreciated.

May I ask you for one more help? Can you help me to understand the formulas? 

=MID(B2,SEQUENCE(LEN(B2)),1)

=CODE(C2#)   [can you help me with # in the formula as well? :) ]

Much appreciated. 

[#8]

@TANACAL125 L

The MID and SEQUENCE functions split the text into its individual characters. The # sign tells Excel to take the entire spilled result of what is in C2. In traditional you would just say =C2 and copy it down.

Look in the attached link to learn more about these Dynamic Array functions.

https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b0... 

[#9]

@Riny_van_Eekelen Wow, that is AMAZING! I read the link you provided and I searched for more youtube videos to digest more in depth on the these new formulas as well. 

Thanks so much for your sharing. You brighten my weekend with this new knowledge. I really appreciated it all. 

And thanks for your coaching. Hopefully, I have more chances to learn more from you. Do I need to mark this post as "completed"? If yes, do you know how? I have tried to look for options but was not successful. Please let me know so I can recognize your effort/help. Thanks,

[#10]

@TANACAL125 You're welcome!! As for closing the thread, I'm not sure. I've never started one myself so have never come to the point of closing one either. But,  you can "Mark an answer as best solution". It doesn't close the thread, though, but you confirm that the answer given solved your problem. And then you always have the "Like" button below every answer to show your appreciation.