Modify an IFERROR INDEX array function to LINK a cell to ROW reference

Good Day

We'd like to modify a function to improve efficiency of the workbook attached

Modify array function in B for ROW link such that the Vlookup in D & E is not necessary & therefore can delete columns A & B

current IFERROR(INDEX($I$1:$I$11161,LARGE(IF($H$1:$H$11161>=LARGE($H$1:$H$11161,1),ROW($H$1:$H$11161),""),ROW(A1))),"")
wanted IFERROR(INDEX($I$1:$I$11161,LARGE(IF($H$1:$H$11161>=LARGE($H$1:$H$11161,LINK(F4)),ROW($H$1:$H$11161),""),ROW(Value or calculation from cells F1-F4)))),"")

Current solution: Vlookup F in column A & report D & E
Column E= end
D = start
C = difference between start & finish

Thanks very much for your help, all comments appreciated

Paul

Hi Xor,

The ROW reference in the B formula that starts in B1 with .....ROW(A1) next is B2... ROW(A2) etc down to ....ROW(A43). 43 is the reference to the total in F4.

Columns D&E basically Looksup the number of ROW(A) in the formula B that is equal to the value given in column F.

EG
43 = ROW(A43) start of the minimum value that is the function in B43
1 = ROW(A1) largest number begins that is the function result of B1
19 = ROW(A19)last of the largest that is the function result of B19 that is given in E1 by looking up F1 = 19

Columns D & E are linked to B to find the reference ROW.

Do you understand now?

Paul

XOR LX,

Sorry you don't understand, I try my best to keep it simple but its not easy for me.
We really tried to be precise with the "Requirement", see below and on workbook and with the best so far ideas = a macro to replace ROW(A1)in the function with contents of cell F.
We don't mind a completely different function but of course we adhere to the forum rules the best we can in how we go about it.

Requirement: the start and end values in column I index of the three variable values in G1 G2 & G3 by position via column H, large & small
G1 = Maximum
G2 = Minimum other than 0
G3 = independent value between G1 & G2 that is not 0
Column H length can vary H1:H60000, generally ascending or 0 (from an IF origin)
The total count of values other than 0 see in F4 can also vary

Thanks again for your comments

Kind Regards

Paul

Okay np, I'll get by with how it is now using Vlookup. It boils down to a simple formula =ROW(), I wanted to put a cell reference (a value inside a cell not the row number of the cell reference) between the brackets but it always returns the ROW number of the cell regardless what the cell contains EG =ROW(F4) will give a value of 4 regardless of what is in the cell, the same as if you go =ROWF4. I couldn't get Concat to work either IE =Row(Concatenate..)

Thankfully, half of the solution is possible as this $F$4 is okay in the Kth part
IFERROR(INDEX($I$1:$I$11161,LARGE(IF($H$1:$H$11161>=LARGE($H$1:$H$11161,$F$4), ROW($H$1:$H$11161),""),ROW(A43))),"")

Regards

ok now solved with

IFERROR(INDEX($I$1:$I$11161,LARGE(IF($H$1:$H$11161>=LARGE($H$1:$H$11161,$F$4), ROW($H$1:$H$11161),""),ROW(A1)+$F$4-1)),"")

Thanks, your comments helped me, went over it again