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

Question

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

riny_van_eekelen · Answer

TANACAL125&nbsp;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.&nbsp;&nbsp;

riny_van_eekelen · Answer

TANACAL125&nbsp;LThe 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.&nbsp;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-205c6b06-03ba-4151-89a1-87a7eb36e531&nbsp;

riny_van_eekelen · Answer

TANACAL125&nbsp;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, &nbsp;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.

steve1330 · Answer

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.

tanacal125 · Answer

Steve1330&nbsp;&nbsp;Thanks for your help. Appreciated.&nbsp;&nbsp;I have tried to use TRIM function to make the correct, but it was not working.&nbsp;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):&nbsp;Do you think if you can provide me suggestion for the last two #N/As? Thanks,&nbsp;&nbsp;

Forum Discussion

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

Share

Resources