The VLOOKUP function is a popular method for extracting data from data lists. Its effectiveness depends on the quality of the data in the data list. I have had many questions from CPAs over the years asking why their VLOOKUP functions don’t work, when it all looks ok.

The major reason involves the quality of the data imported from other systems. Many systems place spaces at the end of codes. It called padding and can be caused because a system has 10 characters available for a data field. If there is a 7 digit code in that field the system may add three space characters to the end of the 7 digit code to bring it up to the character count of 10.

These trailing spaces are hard to spot. To find them press F2 to edit the cell. Examine the cell entry and see where the cursor is. If there is a gap between the last character and the cursor then there are trailing spaces. See image below which has trailing spaces in cell A2.

For an exact match VLOOKUP to work the lookup value must be identical to one of the entries in the first column of the lookup table. Trailing spaces in the first column will stop the VLOOKUP function from working and cause the #N/A to display.

Whilst finding trailing spaces may be difficult, fixing them is easy. The TRIM function is designed to remove both leading and trailing spaces. The TRIM function will not remove any spaces between characters.

=TRIM(A2)

will remove leading and trailing spaces from A2.

The easy way to correct a column with trailing spaces is to use a blank column and enter TRIM functions referring to the same row in the problem column. Then copy the TRIM column entries and use Paste Special > Values to paste the results on top of the problem column.