COL_INDEX_NUM fixed in VLOOKUP in Excel 2007

Hello Friends,

We found something interesting, but also annoying. It seems that Microsoft has changed the way the function VLOOKUP works in Excel 2007.

In my previous Excel version (2000) when I used VLOOKUP, I could later-on insert columns anywhere in the TABLE_ARRAY. Excel increased itself the COL_INDEX_NUM to find the original destination column.

Now in Excel 2007, if I insert a column in the TABLE_ARRAY, the COL_INDEX_NUM stays fixed. My vlookup brings up whatever the column left to the original destination column contains. I find this quite dangerous, it leads to unexpected results, if you don't have a close eye on it.

Please have a look at the attached Excel example, adding a column after column C will change the value of VLOOKUP function in cell A1.

Has anyone an idea, how we could use VLOOKUP the way it worked originally? We tried "$", serched the help docu and the internet and this forum, no findings.

Thank you Mr. Winter for your quick response :-)
The idea of your program is very good,
but quite honestly, I was rather looking for a solution without programming.

I also fear, that it does not solve the issue I have, see example attached in my previous message.