• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting the 2nd and 3rd occurence

J

Jalindah

New Member
I have a problem. I’m trying to populate a field based on an occurence of a value in a range and it works fine. Shown below:

=IF(ISERROR(MATCH(ROUND($E$3,10),’2013-TaskList’!$J$4:$J$5004,0)),”",INDEX(’2013-TaskList’!$K$4:$K$5004,MATCH(ROUND($E$3,10),’2013-TaskList’!$J$4:$J$5004,0)))


But I also want to populate the 2nd, 3rd, and 4th occurence of the same value in the given range and its not working. I need to nest it into the current formula I have but I’m unsuccessful in doing so.


Can anyone help???


Thanks.
 
Hi ,


Getting the 2nd , 3rd , 4th and subsequent occurrences of matching values cannot be done by using only MATCH ; what you need is something like the following :


=INDEX($K$4:$K$5004,SMALL(IF($J$4:$J$5004=$E$3,ROW($J$4:$J$5004)-MIN(ROW($J$4:$J$5004))+1),ROW(A1)))


I have not done the following :


1. Added the ISERROR part

2. Included the sheet names

3. Introduced the ROUND($E$3,10) part


You will have to put in all of those in this template.


As you copy this formula downwards , the ROW(A1) will become ROW(A2) , ROW(A3) ,...


Narayan
 
I'm trying it but it's returning a True as the return value. I don't necessarily need the "round" so I excluded that but I do need the iserror so that the cell is blank if there is nothing to return. There are 2 ranges I'm working from: This is my match range: ’2013-TaskList’!$J$4:$J$5004 This is the range where I want the return value: $K$4:$K$5004. This is the edited formula: =ISERROR(INDEX($K$4:$K$5004,SMALL(IF($J$4:$J$5004=$E$3,ROW($J$4:$J$5004)-MIN(ROW($J$4:$J$5004))+1),ROW(A1)))) But its returning "true".
 
Here is the actual formula and it errors out:


=ISERROR(INDEX(’2013-TaskList’!$K$4:$K$5004,SMALL(IF(’2013-TaskList’!$J$4:$J$5004=$E$3,ROW(’2013-TaskList’!$J$4:$J$5004)-MIN(ROW(’2013-TaskList’!$J$4:$J$5004))+1),ROW(A1))))
 
Hi ,


Sorry , but I forgot to mention that the formula is an array formula , and has to be entered using CTRL SHIFT ENTER.


Please do this and let me know.


Narayan
 
Yes, I did enter it as an array and it's just returning "true". Can you nest the formula into my original formula?


IF(ISERROR(MATCH(ROUND($E$3,10),’2013-TaskList’!$J$4:$J$5004,0)),”",INDEX(’2013-TaskList’!$K$4:$K$5004,MATCH(ROUND($E$3,10),’2013-TaskList’!$J$4:$J$5004,0)))


I'm on a deadline and I can't seem to figure this out...Thanks so much.
 
Hi ,


The problem was with the single quotes ; can you copy paste this formula into your worksheet , do CTRL SHIFT ENTER and see what happens ; if it works , you can introduce the other parts ( IFERROR , ROUND ,... )


=INDEX('2013-TaskList'!$K$4:$K$5004,SMALL(IF('2013-TaskList'!$J$4:$J$5004=$E$3,ROW('2013-TaskList'!$J$4:$J$5004)-MIN(ROW('2013-TaskList'!$J$4:$J$5004))+1),ROW(A1)))


Try this :


=IF(ISERROR(SMALL(IF('2013-TaskList'!$J$4:$J$5004=ROUND($E$3,10),ROW('2013-TaskList'!$J$4:$J$5004)-MIN(ROW('2013-TaskList'!$J$4:$J$5004))+1),ROW(A1))),"",INDEX('2013-TaskList'!$K$4:$K$5004,SMALL(IF('2013-TaskList'!$J$4:$J$5004=ROUND($E$3,10),ROW('2013-TaskList'!$J$4:$J$5004)-MIN(ROW('2013-TaskList'!$J$4:$J$5004))+1),ROW(A1))))


Narayan
 
You must log in or register to reply here.
Back
Top