Column numbers for vlookup

Hi there,

I have found myself using vlookup functions more and more. Now, I have a pretty complex excel model with lookups on various tabs, sometimes to external files.

My issue/question is when I later add a column to a sheet that is within a table array, the formula updates with the new table array values. However, what doesn't update is the column number if I added the column in the area it is "counting". Is there a way to make this more fail-proof when adding columns?

For example:
See the sample sheet I have uploaded. Before tab shows the original table and the lookup function in A2. After tab shows the table with a new column inserted. I know that if I change the index column number to a 5, it will fix the formula. My problem is I have a lot of different lookups and may not know right away if I am breaking something by adding a column. So, I want a solution that does not rely on me going back to fix it.

Any suggestions for a workaround or alternative to vlookup that will do the same thing and be more tolerant to change?

I am on Excel 2007 if that helps with any recommendations. Thanks!

you could try
=VLOOKUP("North",B2:F5,COLUMN(D1),FALSE) column(d4)is = to 4 when you insert this will change to
=VLOOKUP("North",B2:F5,COLUMN(e1),FALSE) so now looks at value in col 5

Thanks. I figured there was a simple way to get around that and it is tough sometimes to take a step back when you are on the go!

One issue with your suggestion, is the column function as you used it is set up to simply provide the number 4 (and increase to 5 when you add in a column to the left of d1. However, I need something a bit more fault tolerant as it would not work on a larger sheet where I am doing a lookup between columns AA and AG and need to add a column between them.

But your idea, led me to the solution I need:
=VLOOKUP("North",B2:F5,COLUMN(E1)-COLUMN(B1)+1,FALSE)

By setting the first column formula to the first column of the table array and the second column formula to the lookup column, this will always work and not be affected by added columns. It also allows for one to not have to count the columns when establishing the lookup equation.

Thanks so much for pointing me in the right direction!

- Brian