One of the drawbacks of using VLOOKUP is that it relies on a numeric value to define which column of the lookup table contains the result.
In the screenshot below, the formula in B2 is =VLOOKUP(B1,E2:I9,5)
This returns the contract renewal date for the EmployeeID stored in B1 because Contract Renewal is the 5th column of the range E2:I9
If you add columns to or delete columns from the lookup table, the formula will break.
In the screenshot below, a column has been inserted to the right of column F which pushes everything one column to the right with the result that Colour is now the 5th column and Contract Renewal is the 6th column of the “lookup table”.
However, in the formula, although Excel adjusts E2:I9 to be E2:J9, it does not adjust the 5 to 6, hence B2 containing the value Space Grey instead of October-2022
In this video, I’m going to show you one simple approach to prevent this happening.