Modify formula to find second match

suprsnipes

Active Member
Joined
Apr 26, 2009
Messages
428
Hi

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

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

Thanks in advance
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 26, 2009
Messages
428
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

Active Member
Joined
Apr 26, 2009
Messages
428
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,360
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top