Forum Discussion
VLOOKUP References to tables with new columns
Hello,
I have a question about shifting vlookup references when adding new columns to the referenced table.
Ex: I have a master data table with all my data
I have a few different tabs with vlookups to said data. If I add columns to the master table, my vlookup references shift the range properly (adds 1 more column) but not the column number.
Original formula:
After the new column add:
As you can see, it adds a new column to my range (AF to AG) but not the corresponding extra column (stays at 31).
How should I write it so it shifts and I can add/delete columns if ever needed?
Changing formulas manually is the absolute last resort as I have about 600 formulas that I would need to manually edit.
Thanks in advance!
Hi, I would suggest to use INDEX/MATCH instead of Vlookup. You will be able to name the specific column you want and then independently whether there are added columns/rows, the correct value will be returned.
Please see attached a sample document with proposed solution.
Martin
cgaechter Try this:
=VLOOKUP([@[ProjectUiD]],DataFULL!A:AF,COLUMNS(DataFULL!A:AF),0)
