Automatically change the col_index_num in vlookup?

[ExcelNoob222]

Hi,

I have a vlookup that currently returns the value in the last column, which happens to be column E currently. So my vlookup is: =VLOOKUP(A2,Data!A:E,5,FALSE)

I have a macro that inserts this formula. However, at some point data will be added to column F and then eventually G and so on. How can I adjust the vba so that the col_index_num automatically changes to the last column of data? So that when data gets added to column F the formula would be =VLOOKUP(A2,Data!A:E,6,FALSE) and then when added to column G it is =VLOOKUP(A2,Data!A:E,7,FALSE)?

Thanks!

 

[RoryA]

That won't work since you also need to amend the A:E part.

I'd suggest you use INDEX and MATCH instead so you only have one range to adjust (the red part):

=INDEX(Data!E:E,MATCH(A2,Data!A:A,0))

If you're doing it in code, use the R1C1 reference style and it's even simpler, assuming you have a variable with the relevant column number in it:

Range("B2").formular1c1 = "=INDEX(Data!C" & colNumber & ",MATCH(RC1,Data!C1,0))"

for example

 

[ExcelNoob222]

That won't work since you also need to amend the A:E part.

I'd suggest you use INDEX and MATCH instead so you only have one range to adjust (the red part):

=INDEX(Data!E:E,MATCH(A2,Data!A:A,0))

If you're doing it in code, use the R1C1 reference style and it's even simpler, assuming you have a variable with the relevant column number in it:

Range("B2").formular1c1 = "=INDEX(Data!C" & colNumber & ",MATCH(RC1,Data!C1,0))"

for example

I should have mentioned I would adjust the A:E part in advance. I would do like A:ZZ

Won't the index match not work either because the Data!E:E column would change as new data is added?

I think I just thought of a way.

In a cell I can do =COUNTA(A2:ZZ2) which would count my headers. Then my vlookup formula would be: =VLOOKUP(A3,Data!A:K,Data!A1,FALSE). As new headers are added with the new data, the count will auto update.

 

[RoryA]

I'm assuming your code has some means of figuring out which column to use?