Index Match only returns first match

[leighnea]

=SMALL(INDEX(detail_check_payment!$A$7:$AA$358,MATCH("S&P ",detail_check_payment!$AA$7:$AA$358,0),1),ROWS(AA7:AA7))

When I use the formula above it only returns the first value that matches and I need it to return a list of all that match.

 

[AhoyNC]

Try:
=INDEX(detail_check_payment!$A$7:$AA$358,AGGREGATE(15,6,(ROW(detail_check_payment!$A$7:$AA$358)-ROW(detail_check_payment!$A$7)+1)/(detail_check_payment!$A$7:$AA$358="S&P"),ROWS($AA$7:AA7)))

Or if you have Excel 365
=FILTER(detail_check_payment!$A$7:$AA$358,detail_check_payment!$A$7:$AA$358="S&P")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[leighnea]

Try:
=INDEX(detail_check_payment!$A$7:$AA$358,AGGREGATE(15,6,(ROW(detail_check_payment!$A$7:$AA$358)-ROW(detail_check_payment!$A$7)+1)/(detail_check_payment!$A$7:$AA$358="S&P"),ROWS($AA$7:AA7)))

Or if you have Excel 365
=FILTER(detail_check_payment!$A$7:$AA$358,detail_check_payment!$A$7:$AA$358="S&P")

I tried both -
the =index gave me a #NUM! result
the =filter gave me a #VALUE!

 

[Fluff]

There is a typo in the filter formula, it should be

=FILTER(detail_check_payment!$A$7:$AA$358,detail_check_payment!$AA$7:$AA$358="S&P")

 

[leighnea]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you I updated it.

 

[AhoyNC]

Did @Fluff change in the FILTER function work for you? I just noticed in your formula it looks like you have a space after the S&P ("S&P "). If so you need the change the formula to show the space.

 

[leighnea]

It is working!! Thank you.

This was my final formula. =FILTER(detail_check_payment!$A$7:$A$358,detail_check_payment!$AA$7:$AA$358="S&P ")

 

[Fluff]

Glad it's sorted & thanks for the feedback.