One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.


After having understood the problem, you can use following formula to overcome this limitation -


Hence, we have replaced the column_index_number with the formula COLUMNS($A:E) which gives number 6. COLUMNS function counts the number of columns within the range contained and this can be used to overcome the limitation.