Copying complex formulas across columns while incrementing row number references

[JTaverna]

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

[#2 · Accepted solution]

@JTaverna 

You may try something like this...

=IF(ISERROR(INDEX($F:$F,30+(COLUMNS($A1:A1)-1))),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))

[#3]

@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.

[#4]

@JTaverna 

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.

[#5]

@Subodh_Tiwari_sktneer 

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 

[#6]

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.