 # formula - if cell contain specific text then return all values in the list

• I would like a forumla to populate G2, G3 and G4, using the data from A and B

Thanks

## Files

• Try this

Code
``=IFERROR(INDEX(\$A\$1:\$A\$11,SMALL(INDEX((\$B\$1:\$B\$11="Fruit")*(ROW(\$B\$1:\$B\$11)-MIN(ROW(\$B\$1:\$B\$11))+1),0),COUNTIF(\$B\$1:\$B\$11,"<>"&"Fruit")+ROW(\$A1))),"-")``
• Another 2 options

=IFERROR(INDEX(\$A\$2:\$A\$10,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1)/(\$B\$2:\$B\$10="Fruit"),ROWS(G\$2:G2))),"")

or if you have dynamic arrays

=FILTER(A2:A10,B2:B10="Fruit")

• At the moment only some 365 users have it.

But I put it there as I have no idea what version the OP is running.

• Glad we could help & thanks for the feedback

• I need to amend this slightly to only bring back the data if there is data in column c.

## Files

• Did the Filter formula I supplied, work for you?

• i used this

1. =IFERROR(INDEX(\$A\$1:\$A\$11,SMALL(INDEX((\$B\$1:\$B\$11="Fruit")*(ROW(\$B\$1:\$B\$11)-MIN(ROW(\$B\$1:\$B\$11))+1),0),COUNTIF(\$B\$1:\$B\$11,"<>"&"Fruit")+ROW(\$A1))),"-")
• And answer came there none.

In that case try

=FILTER(A2:A10,(B2:B10=G1)*(C2:C10<>""))

or

=IFERROR(INDEX(\$A\$2:\$A\$10,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1)/((\$B\$2:\$B\$10=\$G\$1)*(\$C\$2:\$C\$10<>"")),ROWS(G\$2:G2))),"")

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!