Modify formula to find second match

[suprsnipes]

Hi

I would like to ask how I could modify the following formula to find the 2nd or third match, if possible.

=INDEX(P$3:P$100,MATCH(1,IF(1-($I$3:$I$100=""),IF($U$3:$U$100="Y",1)),0))

Thanks in advance

 

[Haseeb Avarakkan]

Hello,

A1 enter

=SUMPRODUCT(--($I$3:$I$100<>""),--($U$3:$U$100="Y"))

A2, Control+Shift+Enter, copy down..

=IF(ROWS(A$2:A2)<=$A$1,INDEX(P$3:P$100,SMALL(IF($I$3:$I$100<>"",IF($U$3:$U$100="Y",ROW(P$3:P$100)-ROW(P$3)+1)),ROWS(A$2:A2))),"")

 

[suprsnipes]

That's great, appreciate it

 

[suprsnipes]

Hi,

Iam using the formula below after getting help from the forum member below, however I would like to stop it from returning #VALUE. How can I do that?

Hello,

A1 enter

=SUMPRODUCT(--($I$3:$I$100<>""),--($U$3:$U$100="Y"))

A2, Control+Shift+Enter, copy down..

=IF(ROWS(A$2:A2)<=$A$1,INDEX(P$3:P$100,SMALL(IF($I$3:$I$100<>"",IF($U$3:$U$100="Y",ROW(P$3:P$100)-ROW(P$3)+1)),ROWS(A$2:A2))),"")

Regards,
suprsnipes

 

[suprsnipes]

Sorry I should have realised the formula is already doing what I want and doesn't return #VALUE like I mentioned in my previous post. Because the formula is referring to another cell when I tried to repeat the formula on another column instead of P I needed to keep the formula linked to the SUMPRODUCT cell also.

Solved, thanks