How to Prevent VLOOKUP Errors when Inserting or Deleting Columns

Bottom Line: Learn how to use the COLUMNS function in VLOOKUP to prevent errors when inserting/deleting columns from your source range.

Skill Level: Intermediate

Video Tutorial

Download the Excel File

If you'd like to practice using the COLUMNS function in VLOOKUP using the same data I use in the video, download my file below.

 VLOOKUP Dynamic Column Index Number.xlsx (108.6 KB)

Prevent VLOOKUP Errors When Columns Change

One limitation when using VLOOKUP is that it can easily return the wrong data when the information in the table array gets rearranged. Adding or deleting a column can mess up your data because VLOOKUP uses the column number when it formulates its answer.

For example, in this image below, you can see on the left that we've set up the VLOOKUP formula to return the email address for each of the names listed. The email address is found in the third column of the table array on the right.

Click to Enlarge

If we add or delete columns and shift those email addresses over, VLOOKUP will continue to return whatever data is found in the that third column.

Click to Enlarge

The COLUMNS Function

There are several ways to fix this problem and the one I'm going to focus on in this tutorial is by using the COLUMNS function. This function returns the number of columns in an array or reference. To use the COLUMNS function just

1. Type the equals sign (=) and begin typing the word “Columns.”
2. Tab into the COLUMNS function (plural, not singular).
3. Select your array. In the example below, I've selected from cell D1 to G1. Because there are four columns in that array (D, E, F, and G), the function returns the value of 4. As I add or delete columns in that array, that value will increase or decrease respectively.

So the COLUMNS function allows our reference to be dynamic. As columns in our range are added or deleted, that number remains the same. The number will identify the data we want because we end our array with the column we are interested in. There may be more columns of data, but we want to end our array with the column we are looking to use.

In our example, no matter how many columns we add or subtract in front of the email column, that column's number will always be the same as the total number of columns in our array (because it's the last column).

Replacing the Column Index Number

This formula that we've created can be used in place of the Column Index Number in our VLOOKUP formula.

One important step when selecting your array is to hit F4, which makes it an absolute reference. (Dollar symbols will be inserted in front of the row and column characters, indicating they are now absolute.)

Another good idea is to select the column headers (if there are any) when you are defining your range for the dynamic Column Index Number. That's because additions and deletions might be made to the data in the columns, but the headers are less likely to change.

By the way, the keyboard shortcut to add a column is Ctrl + + and the shortcut to delete a column is Ctrl + -.

Using the COLUMNS Function with Excel Tables

The COLUMNS function also works using ranges that are formatted as Excel Tables. The formula just looks more like this.

One advantage of using Excel Tables is that you can move columns around (within the existing parameters of the table data) and the VLOOKUP formula will still work correctly.

Pros & Cons

So, to summarize a bit, let me just outline some pros and cons of using the COLUMNS function in your VLOOKUP formula.

Pros

1. This option is really easy to implement.
2. Updating any existing VLOOKUP formulas is easy as well.

Cons

1. The formula can break if columns are moved with regular ranges. 
2. Although moving columns still works with Excel Tables, you still can’t look to the left, or return a column to the left of the lookup column. 

An alternative to avoid these cons is to use INDEX and MATCH and the new XLOOKUP, which you can learn more about in the tutorials outline below.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1