Excel: How to apply a formula to multiple cells while changing only one parameter?

0
votes

I'm trying to apply an INDEX/MATCH formula to multiple cells, and the only parameter I want to change in between cells is the "col_number". It this possible?

Example: =INDEX(Sheet2!$A1:$APH344,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),2)

In the next cell over, I would like to keep everything equal, but change that last "2" to "3": =INDEX(Sheet2!$A1:$APH344,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),3)

Then: =INDEX(Sheet2!$A1:$APH344,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),4)

And so on..

Is there any way to apply this pattern to many cells (i.e. 100s of cells) quickly? Thanks!

4
excelexcel-formulavba
Replace 2 with Column(). You may need to add +1 or similar depending on which column your formula starts. – SJR
Or use column(b2) which will return 2. – SJR
COLUMNS is more rigorous a choice than COLUMN for consecutive integer generation. And the unqualified COLUMN() should be avoided, in my opinion - using COLUMNS negates the need to have to worry about the addition/subtraction of integers depending upon in which column the formula resides. excelxor.com/2014/08/25/… – XOR LX

4 Answers

1
votes

You can use the ROW() function, instead of a static numerical value, to increase the column number as you fill down the column.Here is your original formula using the ROW() function instead : =INDEX(Sheet2!$A1:$APH344,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),ROW()).

If your formula does not start in row one, then you will need to subtract the number of rows that it is removed from row one.For example, if your formula started in row 8: ROW()-ROWS(A$1:A7).

1
votes

That depends on which way you drag your formula,

Dragging from top to bottom:

=INDEX(Sheet2!$A1:$APH344,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),ROW(2:2))

Dragging from left to right,

=INDEX(Sheet2!$A1:$APH344,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),COLUMN(B:B))

0
votes

If you want a cell to be fix, use $ to fix the column and or the row.

For example, to fix the B4 cell, use $B$4 instead. After that just drag the formula to the other cells

0
votes

You are not using $ in your rows.

Give this a try:

INDEX(Sheet2!$A$1:$APH$344,MATCH(Sheet1!$A$2,Sheet2!$A:$A,0),2)

As Ahmed pointed out, the dollar sign $ is placed before both the column and row, when you don't want it to change as you drag a formula to other cells.