Vlookup function not updating when i insert a col.

Hi,

Here is my situation. I have two sheets. One called "Roster" and one called "final". On the final sheet i have cols for each question on the final exam. I also have a total col which sums up the pts for each question.

On the "roster" sheet. This is kinda like a summary sheet. On this sheet i use a vlookup (shown below) in the cells which are supposed to reference the cells on the "final" sheet for the total pts.

Please Login or Register  to view this content.

Here is my problem. Currently the total pts col on the final sheet is 15 cols over. However, if i add another col, which i would do if i added another exam question, the above formula (which is on the "roster" sheet) does not update now to "16"........it is staying at "15" and i dont know why. I thought it would update if i inserted another col on the final sheet.

Can anyone help?

If you need more info just let me know.

Dave

You need to switch to INDEX/MATCH methodology so that it no longer matters where the values are on the second sheet, a MATCH formula can find it, then the INDEX bring back the associated values.

Post up your sheets and we can help you adapt to permanently solve this problem.
(GO ADVANCED and use the paperclip icon to attach your file(s).)

Extending JB's post, this:

=IF(ISERROR(VLOOKUP(A2,Final!$A$2:$Q$68,15,FALSE)),"NA", VLOOKUP(A2,Final!$A$2:$Q$68,15,FALSE))

can become:

=IF(ISNA(MATCH(A2,Final!$A$2:$A$68,0)),"NA",INDEX(Final!$Q$2:$Q$68,MATCH(A2,Final!$A$2:$A$68,0)))

As JB said there are numerous benefits to an INDEX/MATCH approach over VLOOKUP, namely:

a) the criteria value need not reside in the left most column of the range as it does with VLOOKUP

b) inserting columns etc should have no effect
(the ranges should update accordingly ... as long as you don't delete the columns being referenced of course!)

c) INDEX/MATCH as used here has far fewer "dependencies"
(you're only referencing A2:A68 and Q2:Q68 whereas with VLOOKUP you're referencing the entire range A2:Q68)

Note: you could use a MATCH function within the VLOOKUP to ascertain the appropriate Column Number rather than hardwiring the value (15), this would then adapt as columns were added, however, an INDEX/MATCH approach is still better IMO

Where are you using an Array ?

Yes, Arrays adversely impact performance if over used.

Advantage: all the plumbing in one cell.

Disadvantage: can't use a lot of them without having performance issues. Some people not only have a lot OF them, but design arrays that themselves do 1000s of calculations PER CELL. The overhead goes up fast like that.

For me, if the "calculating %" thing appears in the lower left of my screen, I redesign my work until that goes away.

No, no Arrays.

In this sense we're talking about an Array of values... an Array formula to which you're really referring to in terms of avoidance is one where you are required to commit the formula using CTRL + SHIFT + ENTER at which point the formula becomes encased within { } - hence these are often referred to as CSE formulae (a Sumproduct can also be viewed in the same manner as a CSE Array given it is processed by XL engine along similar lines)... see the link to Colin_L's array tutorial in my signature for more info on CSE Array Formulae.