CODEWITHSUNDEEP
excelvlookup
liroch
liroch

Reputation: 59

VLookup sometimes but not always updates column numbers when a column is added

I have an Excel 2016 spreadsheet that is 100 columns wide. The columns are filled with VLookup functions to manipulate cell values and display them on worksheets for operators. In the past, I've added columns within my 100, and the VLookup functions "adjust" -- i.e. the increment by one automatically as I add columns. Today I tried to do the same, add a column within my 100, and the VLookups all were returning values off by one column -- i.e. the formulae did not increment.

It may be that this is a change with Excel 2016 as this is the first time I've made this type of change since downloading the new version.

Here's an example of a simpler formula on my sheet. Other cells have VLookups within VLookups, and complex multi-level nested IF statements, so updating all with the new row numbers will be painstaking. 

=IF(BE2="yes",(IFERROR(VLOOKUP(N2,'Casket, Carrier & Perfect Pack'!$A$2:$N$16,13,FALSE),VLOOKUP(N2,'End-Board Packaging'!$A$2:$P$9,10,FALSE))),"not at cell")

I considered adding row numbers in a new row, and referencing those in my vlookups, which would require a one-time update of all the formulae. However, before I do that I figured I would ask to see if there is another resolution out there.

Can not use VBA for this work due to the operator workstations having macros disabled.

Upvotes: 0

Views: 556

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

I must have missed that version/trick where Excel updated a hard-coded number in the column parameter of a vlookup. I never knew of that capability.

Anyway, since excel will update any references affected by insertion/deletion of rows/column, the way to do it is something like:

=IF(BE2="yes",(IFERROR(VLOOKUP(N2,'Casket, Carrier & Perfect Pack'!$A$2:$N$16,COLUMN('Casket, Carrier & Perfect Pack'!M2),FALSE),VLOOKUP(N2,'End-Board Packaging'!$A$2:$P$9,COLUMN('End-Board Packaging'!J2),FALSE))),"not at cell")

This way, the column parameter will follow/column the chosen header cell for the column in the lookup table.

Upvotes: 1

Related Questions