vlookup function in excel

nher207 · 03-19-2007, 08:17 PM

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** · 03-20-2007, 07:22 AM

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 · 03-20-2007, 08:16 AM

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** · 03-20-2007, 02:45 PM

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 · 03-20-2009, 09:21 AM

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.

03-19-2007, 08:17 PM	#1 (permalink)
nher207 Registered User Join Date: Mar 2007 Posts: 2 OS: XP	vlookup function in excel 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

03-20-2007, 07:22 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,488 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: vlookup function in excel 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

03-20-2007, 08:16 AM	#3 (permalink)
nher207 Registered User Join Date: Mar 2007 Posts: 2 OS: XP	Re: vlookup function in excel 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

03-20-2007, 02:45 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,488 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: vlookup function in excel 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 __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

03-20-2009, 09:21 AM	#5 (permalink)
towangle Registered User Join Date: Mar 2009 Posts: 1 OS: XP	Re: vlookup function in excel 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.