TEXTJOIN and IF Conditions for different columns

[tomlee1260]

Hello.

Using TEXTJOIN, I would like to combine indicators from different cells into a single cell but with different IF conditions:

A. List of indicators with less than 100% accomplishment
B. List of indicators with over 100% accomplishment
C. List of indicators with 0% accomplishment

The indicator with no accomplishment percentage should be excluded from any of the lists generated. If an indicator has an empty cell in the percentage column, the TEXTJOIN result should exclude it.

This is my current formula for List A: {=TEXTJOIN(CHAR(10),TRUE,IF(E2:E9<100%,B2:B9,""))}
It works but the resulting list includes zeroes to represent empty cells in the Indicator Column. How can this be prevented?

Thank you for your assistance.

Screenshot (3973).png

[menem]

Not sure did I understood correctly.
Please try these array formula.

=MID(Concat(IF((ISNUMBER($E$2:$E$9)*($E$2:$E$9>0)*($E$2:$E$9<100%)),CHAR(10)&$B$2:$B$9,"")),2,4096)
=MID(Concat(IF((ISNUMBER($E$2:$E$9)*($E$2:$E$9>=100%)),CHAR(10)&$B$2:$B$9,"")),2,4096)
=MID(Concat(IF((ISNUMBER($E$2:$E$9)*($E$2:$E$9=0)),CHAR(10)&$B$2:$B$9,"")),2,4096)

Regards.

[tomlee1260]

Thank you, menem.

The array formulas do work. Empty cells from Column B did not return 0. However, if a cell from Column E is 0%, the indicator is also excluded. To fix this, I changed the middle of the array formula like this: =MID(Concat(IF((ISNUMBER($E$2:$E$9)*($E$2:$E$9<>"")*($E$2:$E$9<100%)),CHAR(10)&$B$2:$B$9,"")),2,4096)

Thank you so much for your help. My problem is solved.