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.
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
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.
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:
Calculate Number of Days Between Two Dates Excel has a function that calculates the number of days between two dates,…
What is Flash Fill Excel Flash Fill automatically fills your data when Excel detects a pattern in your initial data…
What is Paste Special Paste special is a paste but you can choose the result type. As we all know,…
What is the Excel TEXTJOIN Function? The excel TEXTJOIN function is the best function to concatenate strings in Excel, has…
What is the Excel CONCAT Function? The excel CONCAT function combines all data; it could be a text, a number…
What is the Excel CONCATENATE Function? The excel CONCATENATE function combines text or numbers into a text. CONCATENATE Syntax CONCATENATE(text1,…