#N/A

[MAX]

I have a column with 20 rows and I am entering this function
=INDEX(Av$6:Av$25,MATCH(ROW()-1,$ax$6:$ax$25,)) in the first row then I copy
down till the 20th row. Why from the 17th row it is giving me #N/A.

 

[Mike H]

Max,

It's because it's not finding a match. The ROW()-1 bit is what your tring to
match in AX6 - AX25

If you formula is in Row 6 then that returns 5 and it is obviously finding a
match for 5 but by the time you get to Row 17, ROW()-1 is returning 16 and
there musn't be a 16 in AX6 - AX25 and likewise for 17 to 24.

If you put a 5 in AX6 and a 6 in AX7 and fill the range with the numbers 5
to 24 then the NA's should disappear.

Mike

 

[MAX]

I am using rows from 6 to 25 and I wrote the function in row 6. Now from row
6 to row 25 the function is the same
=INDEX(Av$6:Av$25,MATCH(ROW()-1,$ax$6:$ax$25,))
Just to tell you that AX6:AX25 is a rank column.
Another question about rank, I put Numbers from 1 to 20 (From row 6 to row
20), is that good or there must be a sequence?

 

[MAX]

I found the way, thank you for your great help

I am using rows from 6 to 25 and I wrote the function in row 6. Now from row
6 to row 25 the function is the same
=INDEX(Av$6:Av$25,MATCH(ROW()-1,$ax$6:$ax$25,))
Just to tell you that AX6:AX25 is a rank column.
Another question about rank, I put Numbers from 1 to 20 (From row 6 to row
20), is that good or there must be a sequence?