Copy formula but change row referance

[Matt_Straya]

Hi,
I would like to copy and change a formula, the change being whatever column E says plus one row down (using VBA or formula)
example:

Column E Column F
=Master!$D21 =Master!$D22

I cant use offset as the pattern has breaks in it

Any tips greatly welcomed!

 

[pecoflyer]

Try in col F =INDEX($D$1:$D$100,match($E1,$D$1:$D$100,0)+1)
Adapt ranges as needed

 

[bosco_yip]

Try in col F =INDEX($D$1:$D$100,match($E1,$D$1:$D$100,0)+1)
Adapt ranges as needed

I think this shorter one will get the result as same as pecoflyer's formula :

=INDEX($D$2:$D$101,MATCH($E1,$D$1:$D$100,0))

Regards
Bosco

 

[Matt_Straya]

Hmm couldn't get either one to work. I have attached a workbook with what I am trying to do. I am trying to copy D15 from the "Master" sheet and place it in D5 in the "Minerals_2" sheet. Then, copy D16 from the "Master" sheet and place it in E5 in the "Minerals_2" sheet.

 

[Peter Bartholomew]

An array formula?

 

[bosco_yip]

A normal formula solution

In D5, copied across and down :

=INDEX(Master!$D$13:$D$39,MATCH(LOOKUP("zz",$B$1:$B5),Master!$A$15:$A$41,0)+MATCH($C5,Master!$B$15:$B$41,0)+MATCH(D$4,Master!$C$15:$C$18,0))

Regards
Bosco

 

[Matt_Straya]

Fantastic! both work well. Thank you.

 

[pecoflyer]

In the future please post a sheet immediately so that we don't need to waste our time unnecessarily