Index-Match formula to look for every 6th cell in a column

Question

Is it possible to set the following Index-Match formula to look for every 6th value in the column E? Perhaps it's just the matter of using OFFSET?

Current formula:

=INDEX(E2:E10,MATCH(1,(C13=C2:C10)*(D13=D2:D10),0))

I have 3 columns,

Column D    C     E
    2.30%   3   £3,868.86 
    2.30%   10  £3,736.99 
    2.30%   3   £3,868.86 
    2.30%   2   £3,906.51 
    2.30%   5   £3,807.84 
    2.30%   2   £3,906.51 
    2.30%   10  £3,736.99 
    2.30%   2   £3,906.51 
    2.30%   2   £3,906.51 
    2.30%   5   £3,807.84 
    2.30%   5   £3,807.84 
    2.30%   10  £3,736.99 
    2.30%   3   £3,868.86 
    1.30%   3   £3,685.60

I am using Index and Match function to find which Interest Rate and Deposit % leads to Result in Column E. I need to use my formula on every 6th cell, which will then display selected cells from Column E in any other column.

your question is not clear. You have not used column A in your formula. — nightcrawler23

CallumDA CallumDA · Accepted Answer · 2017-02-16T00:44:33

Firstly, there is no such thing as "Column A6" so I have presumed that you mean Column A. If you actually mean "From A6 down..." then let me know and I will have to update my answer.

Put this in any cell and drag down

=INDEX($A$1:$A$100,(ROW(A1)-1)*6,1)

Alternatively, with OFFSET

=OFFSET($A$1,MAX((ROW(A1)-1)*6-1,0),0)

I'm on a roll now, so with INDIRECT

=INDIRECT("A"&MAX((ROW(A1)-1)*6,1))

Index-Match formula to look for every 6th cell in a column

1 Answers