Microsoft Office Tutorials and References
In Depth Information
You may have trailing spaces at the end of text cells. Although " ABC" and
"ABC " might look alike when viewed in Excel, they cause functions such as
MATCH and VLOOKUP to fail. TRIM removes leading and trailing spaces.
In Figure 11.51 , you can see a simple VLOOKUP in column B. The formula in
cell B2 is =VLOOKUP(A2,$F$2:$G$5,2,FALSE). Even though you can clearly
see that M40498 is in the lookup table, VLOOKUP returns an #N/A! error, in-
dicating that the product ID is missing from the lookup table.
Figure 11.51. This
VLOOKUP should work, but in this instance, it fails.
should work, but in this instance, it fails.
To diagnose and correct this problem, follow these steps:
11. Select one of the data cells in column F. Press the F2 key to put the
cell in Edit mode. A flashing insertion character appears at the end of
the cell. Check to see if the flashing cursor is immediately after the
22. Select one of the data cells in column A. Press the F2 key to put the
cell in Edit mode. Note whether the flashing insertion character is im-
mediately after the last character. Figure 11.52 shows that the
products in column A have several trailing spaces after them. The
products in the lookup table do not have any trailing spaces.