Whenever you are using a function like LOOKUP or VLOOKUP that requires an exact match, often you will find that two cells that look identical are often different due to spaces. Let’s use the example of the cells below containing the word drummer:
They look similar but if we look more closely we’ll see that A1 has no spaces, A2 has a space at the end, A3 has a space at the beginning, and A4 has a space at both the beginning and end. If we try to do a comparison to cell B2 which contains “drummer”, here are the results:
To remove the excess spaces at the beginning and/or end of an entry, use the TRIM function. Let’s insert a column and then use the TRIM function to remove the spaces in the entries in column A and do another comparison in column F:
Now when we compare the results in column B to the entry in C1, they all match. We will use this helpful function nested in a LOOKUP formula in an upcoming post.