I have a workbook with multiple worksheets. On the first worksheet, I have a
cell that is doing a vlookup on a second worksheet. The second worksheet is
a work in progress, and I keep adding columns to it. When I add a column,
the column index number on the vlookup don't change automatically.

I have tried naming the column on the second worksheet, but evidently you
can't use a name for the column index number, but you can use names in the
table array, which doesn't help me here.

So, is there a way to get my column number (col_index_num) to update
automatically when I add columns on my second worksheet?

Probably, but we'd need to know more about your formula/layout to give exact
answer. But here's a start.

Using something like:
COLUMN(D1)
where column D is the column you want a value returned from. This function
currently returns 4. Because of the cell reference, adding/deleting columns
will cause it to change.

Note that if your VLOOKUP table does not start in column A, you will need to
subtract something. Example: if your VLOOKUP is currently C:E, and you're
wanting column E (3rd column of table), your formula becomes something like:
=VLOOKUP(LookupValue,LookupTable,COLUMN(E1)-2,FALSE)

An alternate route, if you want to go with your column heading idea, is to
use MATCH. This structu
MATCH("MyHeading",C1:E1,0)
will return a value of 3 if MyHeading is currently in column E.

Hope this gives you some ideas.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"suthey1" wrote:

I have a workbook with multiple worksheets. On the first worksheet, I have a
cell that is doing a vlookup on a second worksheet. The second worksheet is
a work in progress, and I keep adding columns to it. When I add a column,
the column index number on the vlookup don't change automatically.

I have tried naming the column on the second worksheet, but evidently you
can't use a name for the column index number, but you can use names in the
table array, which doesn't help me here.

So, is there a way to get my column number (col_index_num) to update
automatically when I add columns on my second worksheet?

Perfect! As you mentioned, the Column(d1) option didn't work since I was
adding columns, but the MATCH worked perfectly!
Thanks!


"Luke M" wrote:

Probably, but we'd need to know more about your formula/layout to give exact
answer. But here's a start.

Using something like:
COLUMN(D1)
where column D is the column you want a value returned from. This function
currently returns 4. Because of the cell reference, adding/deleting columns
will cause it to change.

Note that if your VLOOKUP table does not start in column A, you will need to
subtract something. Example: if your VLOOKUP is currently C:E, and you're
wanting column E (3rd column of table), your formula becomes something like:
=VLOOKUP(LookupValue,LookupTable,COLUMN(E1)-2,FALSE)

An alternate route, if you want to go with your column heading idea, is to
use MATCH. This structu
MATCH("MyHeading",C1:E1,0)
will return a value of 3 if MyHeading is currently in column E.

Hope this gives you some ideas.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"suthey1" wrote:

I have a workbook with multiple worksheets. On the first worksheet, I have a
cell that is doing a vlookup on a second worksheet. The second worksheet is
a work in progress, and I keep adding columns to it. When I add a column,
the column index number on the vlookup don't change automatically.

I have tried naming the column on the second worksheet, but evidently you
can't use a name for the column index number, but you can use names in the
table array, which doesn't help me here.

So, is there a way to get my column number (col_index_num) to update
automatically when I add columns on my second worksheet?