Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I automatically update column index number in VLookup whe.
If I insert a column in the range of a VLOOKUP Command, the range adjusts,
but the column index number does not. Is there a work around for this? |
#2
|
|||
|
|||
Use Column() in place of the index number.
For instance: =VLOOKUP(E1,A1:D10,3,0) Can be revised to: =VLOOKUP(E1,A1:D10,COLUMN(C1),0) Where Column(C1) represents the *third* column, *NOT* Column C For example: =VLOOKUP(V1,W1:Z10,3,0) Can be revised to: =VLOOKUP(V1,W1:Z10,COLUMN(C1),0) BUT, in this case, if you inserted a column between W and Z, column C would *still* be column C, and therefore , *still* equate to 3 ! So, you'd have to try something like this: =VLOOKUP(V1,W1:Z10,COLUMN(Y1)-22,0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Gene" wrote in message ... If I insert a column in the range of a VLOOKUP Command, the range adjusts, but the column index number does not. Is there a work around for this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I automatically update column index number in VLookup whe.
A B C D E
Tom 1 All Number Tom 1 Lucy 2 Jessy 3 Bill 4 Singer 5 In cell B2, type: =VLOOKUP(A1,D:E,COLUMNS(D1:E1),0) Now you can do whatever you want to adjust. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically enter date and time but only update once. | New Users to Excel | |||
Naming column in Index Function | Excel Worksheet Functions | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Dynamic Column VlookUps Based on Week Number | Excel Worksheet Functions |