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
Bookmarks