Vlookup with changing columns
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? |
Vlookup with changing columns
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? |
Vlookup with changing columns
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? |
All times are GMT +1. The time now is 09:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com