Forum Discussion
Copying complex formulas across columns while incrementing row number references
I am working on an issue that is driving me nuts. I am trying to copy the formula below across many columns. This is a complex spreadsheet where we are referencing data vertically down through many rows, and then creating a horizontal map across many columns. What I basically need to do is increment the F30 to F31, F32, F33, etc... as the formula is copied across. With no dollar sign, the rows increment from F30 to G30, H30, etc., and with the dollar sign in place before F30, it freezes the F30 so that it shows up in each formula. The H15 is incrementing as it should to I15, J15, K15, etc...
=IF(ISERROR($F30),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))
I would really appreciate any help you can provide
You may try something like this...
=IF(ISERROR(INDEX($F:$F,30+(COLUMNS($A1:A1)-1))),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))
5 Replies
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.
