Fixing N/A Errors in XLOOKUP and VLOOKUP - Hidden Characters Problem

Learn to troubleshoot and fix #N/A errors in Excel's XLOOKUP and VLOOKUP functions caused by hidden characters in this 17-minute video tutorial. Master essential Excel functions including CODE, CHAR, RIGHT, TRIM, LOOKUP, XLOOKUP, ARRAYTOTEXT, REDUCE, LAMBDA, SUBSTITUTE, REGEXREPLACE, REGEXTEST, and UNICODE to identify and remove problematic hidden characters. Discover techniques for detecting hidden characters at the end of lookup values, understand ASCII codes, and implement solutions using TRIM for spaces, SUBSTITUTE for tabs and line feeds, and REGEXREPLACE for multiple white space characters. Follow along with downloadable practice files to learn advanced formula combinations for handling multiple character substitutions simultaneously, complete with practical examples and comprehensive explanations of each solution method.

Syllabus

1. Introduction.
2. Using XLOOKUP and getting an #N/A Error.
3. What cause #N/A? Hidden characters that are unknown.
4. Check for hidden characters at end of lookup value with RIGHT & CODE function to check.
5. Lookup Ascii codes to figure out what it is? LOOKUP function.
6. TRIM to remove Ascii character 32 – a space.
7. SUBSTITUTE and CHAR9 function to remove Ascii character 9 – a horizontal tab.
8. SUBSTITUTE and CHAR10 function to remove Ascii character 10 – a a line feed Alt + Enter in Excel.
9. SUBSTITUTE and CHAR160 function to remove Ascii character 160 – a no-break space.
10. Determining what characters are causing error, when characters could be anywhere in text, not just at end.
11. Extract all characters from text string with LEN, SEQUENCE, and MID.
12. CODE functions to determine the ascii code for hidden characters.
13. Fix lookup value with many hidden white space characters with REGEXREPLACE.
14. Summary
15. Closing
16. Outtake with formula to substitute multiple characters at one time: CHAR, ARRAYTOTEXT, REDUCE, LAMBDA and SUBSITUTE
17. Outtake Summary
18. Outtake Closing