Microsoft Excel Tutorial – Deep Dive on diagnosing VLOOKUP errors.

To download the workbook: https://www.mrexcel.com/youtube/_q0VdM1ES1g/

This is an advanced look at troubleshooting hard VLOOKUP errors. After using TRIM, CLEAN, and making sure there aren’t numbers stored as text, then I use this method to look at the ASCII code, character by character to figure out why the two values do not match.

But today, the vendor part number is reporting that a hyphen is ASCII CODE 63 instead of 45. What is up with this?

I had to modify my workflow to add UNICODE functions in order to discover that the hyphen – which the CODE function reports as ASCII 63 is really Unicode 8208. Why did the vendor decide to use Unicode 8208?

The solution is to SUBSTITUTE( , UNICHAR(8208) with “-“.

Also in this video:
Which characters are actually removed by the Excel CLEAN() function.
This might be our 2500th episode.

Table of Contents
(0:00) Welcome
(0:21) VLOOKUP fails
(0:42) Character-by-Character Compare
(1:39) Explaining ASCII
(1:58) Using CODE() or CHAR() in Excel
(3:03) The hyphens are different
(3:34) Explaining UNICODE
(4:05) Klingon is not in Unicode
(4:43) Using =UNICODE() in Excel
(5:03) Unicode 8208 is a hyphen
(5:18) 28 dashes in Unicode
(5:40) Using SUBSTITUTE to replace Unicode 8208 with a Hyphen
(6:20) What does CLEAN() function really remove
(7:35) Open Questions
(7:55) Why does Excel CODE return ASCII 63 instead of a question mark
(8:20) Thanks for watching 2500 episodes!
(9:02) Like, Subscribe, Ring the Bell