Vlookup function not updating when i insert a col.

[welchs101]

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.

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

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

[JBeaucaire]

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).)

[DonkeyOte]

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

[welchs101]

Thanks for the info.

Sorry for not responding sooner. I am working on about 10 different things at once.

I will review the Index and Match function closer. However, before i do i did have a question. I have stayed away from the "array" functions for some reason. I have used them in the past and for the life of me i cant remember why i decided to not use them. I cant remember if it has to do with speed or something having to do with automatic updating. Are there disadvanges to using array functions?

[DonkeyOte]

Where are you using an Array ?

Yes, Arrays adversely impact performance if over used.

[JBeaucaire]

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.

[welchs101]

The reason i asked about arrays is because both the "match and Index" function have arrays in them..........right?

[DonkeyOte]

No, no Arrays.

[welchs101]

Hi,

Here is what i found online regarding the index and match functions:

Match( value, array, match_type )

Index( array, row_number, column_number )

From what i found these function require arrays. What am i missing?

[DonkeyOte]

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.