Troubleshooting VLookups

WHY ISNT THIS VLOOKUP WORKING?!?! 

Troubleshooting your formulas can be annoying. Here are common reasons you can have an error

 

 The formula result is not the expected value

• Range Lookup is set to true  When range lookup is set to true, excel will return the first result from the closest match. Check if your formula is set to True, 1, or is left blank. If so try updating the last parameter to False
  
• There may be duplicates values in your in your lookup column. A VLookup will return the first match in the data table. Check your data and ensure the values in the first column of your data table are unique. Check out our post on Finding Duplcates for more info.

 

 The formula result is #REF!

Excel will throw a #REF! error when a cell or range that is being referenced is not valid. In the case of a Vlookup, the #REF! error is typically caused when the result column number is greater than the number of columns found in the data table. For example, the image below has a data table containing two columns of data.

If we tried using the following formula,  =VLOOKUP("1001",A:B,3,FALSE),  Excel would throw a #REF! error because the data table does not contain three columns.

 The formula result is #N/A

We saved the best for last and of course by best I mean the error most likely to have you pounding on your keyboard! An #N/A error will be thrown when a match is not found. This can be caused by a number of reasons so lets dive into them

• The value being looked up is not in your lookup column. This one's a bit obvious but the frist step is to check the data you are looking exists in your table.
  
• There are trailing or leading spaces in your data. Sometimes data can have extra spaces which can cause unexpected behavior when writing formulas. A quick check would be to click on the applicable cell in the data table and highlight the value in the formula bar. If the highlight extends past (or before) the text then we know the problem is spaces. 
  
  
  
  To fix we can either clear the spaces out of the text in our data table or modify the VLookup to include the spaces. In the example above we would change to =VLOOKUP("1001     ",A:B,2,FALSE) to normalize with our data
  
  
  
• The data you are you looking up is stored as a different type. Excel stores each value in a cell as the appropriate data type. 123 would be stored as a number while ABC would be stored as a text. Sometimes however depending on the structure of the data we can have a mismatch between the value in the cell and its data type. Most commonly this is the case with numbers that act as a dimension (eg: ID, Invoice Number, Reference Number, etc). When doing a VLookup we want to make sure that the lookup value is in the same data type as the values in the lookup column.
  
  Example: the ID column is stored as text. This is identifiable by the green triangles in the upper left column. Our VLookup in cell I1 is attempting to run the find value 1001 instead of "1001". Because no number 1001 exists in the table, the formula is returning #N/A
  
  
  To fix we can either wrap the number in quotes and update the formula to =VLOOKUP("1001",A:B,2,FALSE).