# Formula error in Return of multiple matches ..

#### Prasenjit Basumatary

##### Member
Hello everyone,
I have written a formula to return multiple matches but it does not work. Request you to check the formula and help me to rectify it. In my attached workbook, the formula was written in yellow marked cells.

#### Attachments

• 8.9 KB Views: 6

#### bosco_yip

##### Excel Ninja
Change your E3 array formula, from this >>

=INDEX(\$B\$3:\$B\$8,SMALL(IF(D3=\$A\$3:\$A\$8,ROW(\$A\$3:\$A\$8)-2,""),ROWS(E\$2:E2)))

Into this >>

=INDEX(\$B\$3:\$B\$9,SMALL(IF(D3=\$A\$3:\$A\$9,ROW(\$A\$3:\$A\$9)-2,""),COUNTIF(D\$3:D3,D3)))

and, array-enter copied down.

Regards
Bosco

Last edited:

#### p45cal

##### Well-Known Member
Try this tweak, array-entered in cell E3, then copied down:
Code:
``=INDEX(\$B\$3:\$B\$9,SMALL(IF(D3=\$A\$3:\$A\$9,ROW(\$A\$3:\$A\$9)-2,""),COUNTIF(\$D\$3:\$D3,\$D3)))``
Array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.

post-posting edit: shucks, bosco_yip beat me to it!

#### Prasenjit Basumatary

##### Member
Thank you to both of you for your support and help. It is working perfectly.