Begin Index and Match formula from the 11th row.

[omairhe]

Hello, in excel how do I set the index and match formula to offset
or begin with row 11, so that if the user inserts a new row at row 10,
the match and index must not change to the cell reference of row 12.

Also I am avoiding to take the entire column as my index formula
because I will be getting circular reference in the data which is located
at row 1 to 10.

my formula in cell D3 =IFERROR(INDEX(D:D,MATCH(M7,B:B,0))+1,1)

Will appreciate any help. Thank you.

 

[Peter_SSs]

See if something like this would suit you

=IFNA(INDEX(INDEX(D:D,11):D1000,MATCH(M7,INDEX(B:B,11):B1000,0))+1,1)

 

[omairhe]

Many thanks Peter.

 

[Peter_SSs]

You're welcome.