VLOOKUP REF Error because one or more table_array column is deleted
Deleting table_array column causes VLOOKUP REF error when the col_index_num value becomes greater than the number of columns in table_array. This error occurs when VLOOKUP formula has already written; then someone deletes one or more column in table_array.
For example, there is data as shown below.
The VLOOKUP function is used to find the prices on the bill with the col_index_num value is 2 (price for tall size). When will the VLOOKUP REF error occur?
No REF error when venti price deleted. The number of table_array columns changes from 4 columns to 3 columns, still greater than the col_index_num value.
Pay attention to the price on the bill, still showing the price for the tall size.
No error too when Grande price deleted. The number of table_array columns changes to 2 columns, equal to the col_index_num value. Prices on the bill keep showing prices for a tall size.
VLOOKUP REF error occurs when the number of table_array columns only leaves 1 column, less then col_index_num value.
VLOOKUP REF Error because the col_index_num value is greater than the number of columns in table_array
This error occurred not because the table_array column was deleted. This error appears more often when writing the VLOOKUP function for the first time. If the col_index_num value is greater than the number of table_array columns, VLOOKUP REF error immediately appeared.
Look at the image below. The VLOOKUP function in cell H4 returns a #REF! error, because the col_index_num value is 5, greater than the number of table_array columns (4 columns).
The solution is easy. Use a col_index_num value less than or equal to the number of table_array columns
Be careful when deleting the table_array columns
When deleting a table_array column in the example above, the prices on the bill does not change, keep showing the price for the tall size. What if the first deleted column is for the tall price column?
Did an error occur?
There was no error because the col_index_num value is still smaller than the number of table_array columns.
But, look at the prices on the bill — all prices changes, showing the prices for a grande size. Prices change automatically is a dangerous condition, no error message but the billing prices changes.
The Solution
Use VLOOKUP MATCH Formula to avoid REF error, and automatically change for VLOOKUP return values when someone deletes one or more table_array columns.
Read the article below for a detailed explanation.
There are many possible errors when using the VLOOKUP function. For errors other than REF error please read the following article:
