Quote Originally Posted by rberke


But everything in excel has to be used carfully. For instance, a lot of people are in love with vlookup. But if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false).

I always use the match and index functions to avoid the vlookup problem. I would Name lookupkey=A:A lookupanswer=B:b and then use

=Index(lookupanswer,match(a2,lookupkey,false). Now I can enter a column anywhere I want and things work just fine.


It's the index in the VLOOKUP that prompts me to avoid using it in general. The regular ole LOOKUP function seems to be better suited to maintaining its lookup column point of reference. In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.