# vlookup function in excel



## nher207 (Mar 20, 2007)

Hi,

I am proficient with the excel vlookup function. i know how to lock ($$) cells into place. What messess me up is if i do a vlookup, it works, everything is fine, and then i go and insert a column inbetween the range of cells that i vlookup-ing, this throw off my return value well. whereas every other formula in excel "adjusts" when a new column or row is added, it seems that the vlookup says the same, and you have to go back and manually change the formula. i know about paste-special values, this is not what i want. I want to be able to leave in the vlookup formula in the cell, and insert columns and have the formula adjust as needed. any suggestions would be greatly appreciated!!!!

Thanks
Noah


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome to TSF.

Although VLOOKUP will likely adjust the ranges, assuming you use relative references rather than absolute ones, it uses a column number as part of the lookup. Therefore if you add in another column, how will it know which column you want to use? Any time I've used Vlookup, I always try and keep the main table static.


----------



## nher207 (Mar 20, 2007)

how do you use abosolute references? is that when you lock in the cell range with the dollar sign? if so, that does not work either. when you say excel will "likely adjust the ranges", it NEVER adjusts the ranges, it stays put. keeping the data sheet static is not an option in my line of work, and i do understand the issue with vlookup. if every other formula can adjust....but vlookup cant? there has to be away around this.
thanks


----------



## Glaswegian (Sep 16, 2005)

Apologies - I should have explained- I tend to name any data tables - then the formula will adjust. However, there is still the column issue and naming a range will not help. The only way round that would be to use an INDEX/MATCH formula.

Some good examples of both here

http://www.ozgrid.com/Excel/dynamic-lookups.htm


----------



## towangle (Mar 20, 2009)

One work around for the original question is to add a row above the vlookup table that numbers the columns in the table. Rather than entering the column # directly in the vlookup formula, reference the column number, with row and column locked (example: $C$3). When you insert a column, update your column numbers and your formulas will work again.


----------

