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.
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.
Daniel Gobeille commented
Perhaps the solution below is not perfect but it sure is a brilliant workaround.
It will save me loads of time. I should have thought of it myself, its that simple.
I find myself relying on others brains like I did here. :-)
On top of all columns make an extra row with numbers (1, 2, 3...) that match the VLOOKUP matrix. When you use VLOOKUP instead of putting in the number of column (e.g. =VLOOKUP(value,matrix,4,false) ), call that top cell that contains the number of column (e.g. =VLOOKUP(value,matrix,C1,false) ). Whenever you insert a column, just refresh numbering in the top row and it will work :) Hope I was clear enough
On top of all columns make an extra row with numbers (1, 2, 3...) that match the VLOOKUP matrix. When you use VLOOKUP instead of putting in the number of the chosen row (e.g. "4"), call that cell (e.g. "D1"). Whenever you insert a row, just refresh top row numbering and it will work :) Hope I was clear enough.
This is a major issue. If I insert a column in a table I should not have to go and update every cel that has a vlookup
A.C. WILSON commented
I believe that implementing the more generalized 2-dimensional look-up "GETMATCH()", as suggested elsewhere in this site, will be a better use of MS's Excel-staff resources than making any further tweaks to the VLOOKUP/HLOOKUP, which are fundementally more limited.
Alexander Duplessie commented
Kenneth, your solution below with Columns() does a neat job of solving this problem when I am making the worksheet. I do still wish that VLookup and HLookup acted like SumIf formulas (changing the column reference with insertions and deletions) in this respect though.
I work with a number of pre-existing documents that use V/H-Lookup heavily, making inserting and deleting a huge pain.I also can't count on others using the Columns() solution or similar, making editing others' documents similarly painful. These functions stand out negatively for not incrementing automatically.
Kenneth Barber commented
Colin Delane showed me a more robust way to avoid hard-coding the index:
Using this formula, your lookup column doesn't have to be to the left of your return column.
However, the formula below
gets the same job done with the same robustness and is much shorter.
Kenneth Barber commented
It's your own fault for hard-coding the index. Try this:
For a more robust approach, try INDEX and MATCH together.