Question: I’ve used a VLOOKUP formula, but it’s not working and is returning an error. How do I fix it?
Answer: By checking the type of error that’s been returned.
Process (Excel 2003, 2007, 2010):
The two most common types of errors that will be returned by an Excel formula are #REF and #N/A. Each of these, while an error, is caused by something different. In this example, we’ll be using a basic pivot table and a VLOOKUP formula.
1. #REF
A #REF error returns when a formula contains an incorrect cell reference. This can happen in many ways, but most often occurs when:
- A column, row or named cell used in the formula has been deleted
- A formula has been moved or copied to another cell and the cell references are incorrect
I have selected column B, then deleted the entire column (keyboard shortcut CTRL + ‘-‘ (minus)). With the column containing the scores now gone, cell E2 (originally F2, but with the B column deleted, the cell has shifted to the left) now shows a #REF error.
The easiest way to fix a #REF error is to undo the step that removed the column, row or cell or correct the cell references (or named ranges) that have been changed in the formula.
2. #N/A
A #N/A error is common when working with Lookup and Reference formula. It is returned when the reference that is being searched for has not been found in the column referenced. In the example, I have referenced a student that is not listed in column A and a #N/A error is returned in cell F2.
A #N/A error does mean that the formula is working, however either:
- The column being searched is not correct i.e. it doesn’t contain the information that should be searched on; or
- The value that is being searched on does not exist in the range.
When searching for a value, and using the EXACT parameter, the values must match entirely. If there is an extra space at the end of the value, it may lead to there not being a match if the original value has no extra space.
To fix this error, check the formula and confirm that the column to be searched is correct, and that the value that you’re searching on matches exactly the value in the column e.g. no extraneous spaces.
