Inserting text to cells based on conditional formatting

I want to add a list of words in the part of the column under "IRU" (located in H216), as shown here:
iru.png

If cells B20-B131 contain "IRU" (with exact capitalization), I want to add the word from the adjacent cell to the list. For example, given that cell B124 (highlighted in green) contains "IRU": cell.png, I want to add the value in cell A124 (polyester): A.png to a cell under IRU as shown earlier. What formula can I use to accomplish this?

Edit: also attached is the excel sheet.

Try this formula:

Formula:

Please Login or Register  to view this content.

I suppose that you have IRU text in C1

Originally Posted by mhanna112

This only works for the first match. Can I alter the formula to retrieve all matches, rather than just one?

In this particular case, without VBA, in column C in first cell type the word you want("IRU") and in C2 insert the formula

Formula:

Please Login or Register  to view this content.

And then sort by this column. The cells with values will appear at top and they are associated with text in column B that contain "IRA"
Otherwise, if you need the values to be extracted in other worksheet than, is better to use VBA!

I suggest that you upload a sample WB - see the yellow banner above to see how to

There are no manually added expected results in your sample file, so it remains as clear as mud (to me).

In H217

=IFERROR(INDEX($A$2:$A$131,AGGREGATE(15,6,(ROW($A$2:$A$131)-1)/(FIND("IRU",$B$2:$B$131)>0),ROWS($1:1))),"")

Copy down