Vlookup function not updating when i insert a col.

**DonkeyOte** · 05-06-2009, 03:07 AM

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** · 05-06-2009, 04:49 PM

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?