VLookup and Inserting Columns: VLookup should increment the col_index_num when you insert a new column in the range.
When you insert a column and it affects the range referred to by a vlookup, the table range updates but the column index number does not. The column index number should increase with the table range so that the same column is pulled. I can't think of a scenario where inserting columns in a table but not updating the column index is a user's intended course of action.44 votes
Since the XLOOKUP function is now available in Excel to O365 subscribers, we recommend using it to accomplish what you need to do. With XLOOKUP, there’s no index number. You provide a range reference, so it will automatically adjust if you insert a column that causes the range to move.
- Don't see your idea?