How to Prevent Excel Vlookup Error when Inserting or Deleting Columns (#REF!)

If you’re using Vlookup in an Excel spreadsheet and you insert or delete columns in the table that the Vlookup is referencing, you may see a “#REF!” error in the Vlookup result. This is because the Vlookup function uses the column number as an argument, and when you insert or delete columns, it changes the column numbers and the Vlookup may not be able to find the data it is looking for.

To prevent this error, you can use the INDIRECT function in your Vlookup formula. The INDIRECT function takes a text string as an argument and returns a reference to the range specified by the string. You can use the INDIRECT function to create a reference to the table that the Vlookup is using, and then use that reference as the Vlookup’s table array argument. This will make the Vlookup more flexible and allow it to handle changes to the table without causing errors.

Here’s an example of how to use the INDIRECT function in a Vlookup formula:

=VLOOKUP(A2, INDIRECT("A1:B10"), 2, FALSE)

In this formula, the INDIRECT function creates a reference to the range “A1:B10” and passes that reference to the Vlookup function as the table array argument. This means that the Vlookup will look for the value in cell A2 in the range “A1:B10”, and return the value from the second column of that range.

If you insert or delete columns in the table, the INDIRECT function will automatically adjust the reference it is creating, and the Vlookup will continue to work without causing errors.