VLOOKUP, one of Excel's most popular functions, is used frequently but often returns errors (even though a manual lookup would succeed). Why? Here we break down several common causes of VLOOKUP errors.
As shown below, the formula in E2 is correct and the references are fine; you should check whether the lookup value or the cells in the lookup range contain spaces.
Solution: press Ctrl+H » Replace. In 'Find what' enter a space, then click 'Replace All'.
Solution: press Ctrl+H » Replace. In 'Find what' enter a space, then click 'Replace All'.
If the data source is exported from a system, invisible characters may be present. The solution is:
- Data » Text to Columns
- Delimiters
- Completed
- This method can remove most types of invisible characters.
As shown below, the following formula is incorrect:
- This is because VLOOKUP requires the lookup value to be in the first column of the lookup table. In the data table on the left, 'Name' is in column B, so the lookup range must start at column B. But the formula was written starting at column A, so VLOOKUP only searched A1:A5.
- This problem mainly occurs with lookups involving numeric types. Look at the formula in cell E2 below — it needs to look up the corresponding Number based on cell D2:
- The codes in column D are numbers stored as text, while the lookup codes in column A are numeric values in General format, so the lookup fails.
- The solution is to make the lookup range and the lookup value the same format.
- Modify the formula to convert the lookup value to a number by multiplying it by 1, then the lookup works: =VLOOKUP(D2*1,A:B,2,0)
- Conversely, if you want to convert a numeric lookup value to text, append an empty string: =VLOOKUP(D2&"",A:B,2,0)
