Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted
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 firstname.lastname@example.org as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.