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.