Forum Discussion
Copying complex formulas across columns while incrementing row number references
You may try something like this...
=IF(ISERROR(INDEX($F:$F,30+(COLUMNS($A1:A1)-1))),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))
Subodh_Tiwari_sktneerYES! this has solved the problem. I am always curious to learn new things and the logic behind this would be helpful.
(INDEX($F:$F,30+(COLUMNS($A1:A1)-1)) replaces ($F30) in my formula
Can you explain the logic for each step within this new part of the formula? I understand the end result has solved the problem, but not sure this code makes it happen.
When you place the formula in the first cell, the index part of the formula is this...
INDEX($F:$F,30+(COLUMNS($A1:A1)-1))
In the above formula only COLUMNS($A1:A1)-1 part is dynamic and the rest of the Index formula won't change if you copy it across the columns. Right?
If you pay attention to the part...
INDEX($F:$F,30
This is equivalent to F30 as Index is looking at column F where row index is 30.
But in the formula (COLUMNS($A1:A1)-1) is added to the row number and in the first formula cell, COLUMNS($A1:A1) will be evaluated as 1 and COLUMNS($A1:A1)-1 will be evaluated as 0 so nothing will added to the row index 30 in the formula in the first cell and Index formula will be converted to INDEX($F:$F,30) only and will refer to the cell F30.
But when you drag the formula across the next column, (COLUMNS($A1:A1)-1) would change to (COLUMNS($A1:B1)-1) which is equivalent to 2-1 i.e. 1 and this 1 will be added to the row index 30 to make it 31. So the Index formula will become Index($F:$F,31).
Similarly when this formula is copied to the next column it would become Index($F:$F,32) and so on and so forth.
Hope this helps you to understand the formula better.
If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.
Thank you, this makes sense and the formula is changing exactly as you describe, and working perfectly.
My last question is why (COLUMNS($A1:A1)-1)?
Is (COLUMNS($A1:A1), which is = 1, basically an incremental counter? Is it searching for how many columns are between the fixed $A1 and A1 = 1? I can see how it increases by 1 each time it is copied to give the desired result. Could I replace Columns with Rows to get the same result?
Thank you, just trying to understand better
You're welcome JTaverna!
That's correct.
COLUMNS($A1:A1) here is being used as a counter only. You may use $B1:B1 or $C1:C1 or something else to generate a counter 1 in the first formula cell which is incremented by 1 as the second column reference is relative which changes when you copy the formula across.