This is a common problem that interns and coworkers often approach me with.
“Can you take a look at my vlookup? I’m doing everything right but getting errors.”
The culprit: incorrectly formatted data.
The Problem
Cells in Excel can be formatted in different ways. (Hint: see how your cells are currently formatted by keying Ctrl + 1.)
Some of these formats don’t interact so well together. The most notorious clash of formats is text vs. numbers.
What’s with all those green triangles?
Sometimes when exporting data from another software system into Excel, values will be formatted as text. This will cause your vlookup to stop working.
Your lookup value is likely stored as a number, not a text. A 6 stored as a number is treated differently than a 6 stored as a text.
Fortunately, it is easy to tell when data is stored as a text. Excel will alert you with a green triangle on the upper-left hand side of each cell containing text. Click on it and you will see the following message.
Now, select all the cells formatted text and click on the yellow exclamation point. Select “Convert to Number” to switch formatting.
If that didn’t work…
There is another way to convert to text to numbers: multiply the cell by 1. This is a good trick to know because you will not always get the above warning from Excel.
Leave a Reply