|I'll start at the beginning, only because I don't know what you already know.|
The first thing to keep in mind is that the COLUMN() function is not required for the INDEX() function. It is simply used as an automatic way to come up a number that can serve as the column_num argument for the INDEX() function. I understand that the use of the word "column" in so many different ways can be confusing.
- There are Columns in a spreadsheet e.g. A, B, C, which are also 1, 2, 3.
- There are columns in an array e.g. In the array $D$24:$K$35, spreadsheet Column D (which is the same as spreadsheet Column 4) is array column 1, spreadsheet Column E (which is the same as spreadsheet Column 5) is array column 2, etc.
- There is the COLUMN() function which returns the number of the spreadsheet Column. A=1, B=2, etc.
- There is the column_num argument that refers to an array column.
I will do my best to refer to spreadsheet Columns with an uppercase C and array columns with a lowercase c to avoid confusion.
Let's look at the COLUMN() function first:
There are 2 ways to use the COLUMN function. One is to include a reference and one is to not. If you use a reference to a cell, the function will return the Column number for that cell.
With a reference:
=COLUMN(D6) will return 4 because Column D is the 4th Column of the worksheet. It doesn't matter where in the spreadsheet you put =COLUMN(D6), it will always return 4 because of the reference to a cell in Column D.
Without a reference:
=COLUMN() will return the Column number for whichever Column the function is used in. e.g. If =COLUMN() is used in Column D, it will return 4, if =COLUMN() is used in Column AJ, it will return 36.
Now, since the function returns a number, you can perform mathematical operations on the result.
If you put =COLUMN()-3 in Column D, you will get 1 because 4 - 3 = 1.
If you put =COLUMN()*2 in Column AJ, you will get 72 because 36 * 2 = 72.
Now, regarding the use of the COLUMN() function in the INDEX function:
The INDEX function requires a column_num argument. The key thing to remember is that the column_num argument refers to a column in the array that is referenced by the INDEX function, not a spreadsheet Column.
For example, in the INDEX function I suggested, the referenced array is $D$24:$K$35. Spreadsheet Column D is array column 1, spreadsheet Column E is array column 2, etc. Therefore, if you want to extract data from Column D (in your case), you have to use a column_num argument of 1 because the column_num argument refers to the column in the array $D$24:$K$35, not Column D of the spreadsheet.
So, if you want to refer to the first column of the array $D$24:$K$35, you need a column_num argument of 1. If you put the INDEX function I suggested in Column D, the COLUMN() function portion will return 4. As noted earlier, 4 - 3 = 1, so your column_num argument resolves to 1. When you drag the INDEX function over to Column E, you will get 5 - 3 = 2, etc. That's what makes the use of the COLUMN() function so helpful/powerful. It will increment the column_num argument by 1 each time, allowing you to pull data from contiguous cells, as shown in your example.
You could hard code those column_num arguments into each individual formula - and in some cases you might have too. For example, if you wanted to pull data from non-contiguous cells or from cells in different order than they are in the array e.g columns 4, 2, 1, 3, etc. it might be difficult to find a "helper" function to create the column_num argument since there is no pattern that Excel can follow.
The overarching concept here is that seemingly unrelated Excel functions can often be combined/nested in order to get Excel to perform tasks that might not seem possible just by looking at the functions individually. In this case, we are using the MATCH function to determine the row_num argument for the INDEX function and the COLUMN() function to determine the column_num argument.
As for your immediate issue, regarding the use of the INDEX function I suggested in a different cell or to reference a different table, my assumption is that you need to change the "-3" to something else if you want to use the COLUMN() function to determine the column_num argument of the INDEX function. As long as COLUMN() "plus or minus some value" resolves to 1, it will refer to the first column of the array referenced by the INDEX function. When it resolves to 2, it will refer to the 2nd column, etc.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.