# Modify formula to find second match

#### suprsnipes

##### Active Member
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))``

### 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
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
That's great, appreciate it

#### suprsnipes

##### Active Member
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
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

Replies
8
Views
116
Replies
24
Views
1K
Replies
1
Views
62
Replies
4
Views
91
Replies
2
Views
164