# How to control which values are incremented on drag down?

I have a complex formula in a cell with various references to other cells.

=INDEX($Sheet1.B1:B100;MATCH(C1;$Sheet1.D1:D100;0))


I want all cell in the same column to have the same formula with only one reference incremented. But if I select the field and drag down the little square all references are incremented. So the following fields would have the values:

=INDEX($Sheet1.B2:B101;MATCH(C1;$Sheet1.D2:D101;0))
=INDEX($Sheet1.B3:B102;MATCH(C2;$Sheet1.D3:D102;0))
=INDEX($Sheet1.B4:B103;MATCH(C3;$Sheet1.D4:D103;0))
…


But what I need is:

=INDEX($Sheet1.B1:B100;MATCH(C1;$Sheet1.D2:D100;0))
=INDEX($Sheet1.B1:B100;MATCH(C2;$Sheet1.D2:D100;0))
=INDEX($Sheet1.B1:B100;MATCH(C3;$Sheet1.D2:D100;0))
…


That means all matrix should not be incremented, the single reference to column C has to be incremented. How can I do this, where it is not possible to to it manually as too many rows are affected?

edit retag close merge delete

( 2018-01-12 15:56:16 +0200 )edit

Sort by » oldest newest most voted

Or you could use absolute references for the fixed ranges in your initial formula:

=INDEX($Sheet1.$B$1:$B$100;MATCH(C1;$Sheet1.D1:D100;0))


If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

more

+1 That's even less work... assuming the second range is also meant to be an absolute reference $Sheet1.$D$1:$D$100 ( 2018-01-12 15:27:05 +0200 )edit Hello @gunwald, You could give a fixed Name to your two ranges $Sheet1.B1:B100 and \$Sheet1.D2:D100, via the menu Sheet : Named Ranges and Expressions : Manage... ( CTRL+F3 ).

Suppose you named your ranges “Sheet1_B1_B100” and “Sheet1_D2_D100” respectively, then you could put the formula:

=INDEX(Sheet1_B1_B100;MATCH(C1;Sheet1_D2_D100;0))

and drag the cell handle downwards so that only C is incremented.

HTH, lib

more