If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#N/A
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. |
#2
|
|||
|
|||
#N/A
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" wrote: 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. |
#3
|
|||
|
|||
#N/A
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? "Mike H" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
#N/A
I found the way, thank you for your great help
"MAX" wrote: 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? "Mike H" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|