The old list is in column A, and the new list is in column D. The MATCH formula in column C appears to work in cell C2. Adam Alton appears in cell D2 but isn’t anywhere in column A. The formula also appears to work in C4, which finds that the name in cell D4 appears in cell A3. But for some reason, the formula isn’t detecting that Billy Ray Smith in cell D3 can be found in cell A2. Similar problems happen in cells C6 and C7.
Everything in cells A2 and D3 looks exactly the same to the human eye. Why is the MATCH function not seeing them as a match?
TRAILING SPACES
Whenever I encounter a VLOOKUP or MATCH function failing, my first impulse is that one cell has trailing spaces. The quick way to test is to select cell A2 and press the F2 key to put the cell in Edit mode. Then check if the flashing insertion cursor is next to the last letter or if it’s a space or more to the right of the last letter. You also could use a formula such as =LEN(A2) and =LEN(D3) to learn how many characters are in each cell.
If you do encounter spaces at the end of one column and not the other column, use a formula such as =TRIM(A2) to remove extra spaces. TRIM will remove any leading spaces or trailing spaces and will replace repeated spaces with a single space. For example, =TRIM(“ FIRST LAST ”) would return FIRST LAST.
Enter the TRIM formula in a blank column adjacent to your data. Copy the cells containing the TRIM formula and use Paste Values to replace the original data with the corrected data.
In this instance, however, the data didn’t have leading or trailing spaces. Something else was happening.
NONBREAKING SPACES
It turns out that the company in Nashville had recently upgraded from an old employee tracking system to a new, cloud-based system. The data in column D was downloaded from the web. Because web browsers will ignore repeated spaces, many websites will generate something called a nonbreaking space instead of a regular space.
In order to check if the spaces are causing the problem, set up two columns that reveal the ASCII character code for each character in cells A2 and D3.
While you can search the web for a table of ASCII codes, it helps to remember a few basics: Capital letters A through Z run from code 65 to 90. Lowercase letters run from 97 to 122. Digits 0 through 9 run from 48 to 57. Most of the punctuation in the top row of your keyboard is in the 33 to 47 range, and a space is character 32.
Cells H14 and H18 show that column D is using character 160 for spaces instead of character 32. While these spaces look the same, the VLOOKUP and MATCH functions don’t see them as being equivalent. When VLOOKUP was written 35 years ago, there were only 128 characters—so accounting for a character 160 or a different kind of space was never considered.
REPLACING NONBREAKING SPACES
Once you’ve determined that your problem is nonbreaking spaces, how do you convert them to regular spaces? The Excel CLEAN function is supposed to remove nonprinting characters, but it has never been updated to deal with character 160.
Find and Replace seems like a good choice, but how do you type a nonbreaking space into the Find What box of the Find and Replace dialog? If you have a number keypad on your computer, you can follow these steps:
- Select column D.
- Press Ctrl+H to display the Find and Replace dialog.
- Click inside the Find What box.
- While holding down the Alt key, use the number keypad and type 0160. There will be a half-second pause and then a space will appear. Note that you can’t use the numbers on the top row of your keyboard. You have to use the number keypad keys.
- Tab to the Replace With box. Use the spacebar to type a regular space.
- Click the Replace All button. The MATCH formulas will start to work.
If your laptop doesn’t offer a number keypad, you could use =SUBSTITUTE(D2,CHAR(160),” ”) in column E. Enter the formula and then Paste Values to column D to replace the nonbreaking spaces with regular spaces. Or carefully copy the nonbreaking space from a cell in column D and paste that into the Find What box.
January 2017
