# How to create a Dynamic List that Automatically Lists Items that Match Certain Criteria

#### franswa3434

##### Board Regular
Hi,

I am trying to create a dynamic list that automatically lists items that match certain criteria. With my example below, I want all the blue and green items (as labeled in Column B) in Column A to be put into a list in Column E. This list also needs to be dynamic as more items are added to filter through and add only the "blue and green" items to the list.

Thank you anyone and everyone who is willing to help!
 A B C D E Item Color Blue and Green Items Grass Green Grass Water Blue Water Fire Red Sky Dirt Brown Go Light Sky Blue Car Go Light Green Car Blue

<TBODY>
</TBODY>

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi.

Put this formula in C1:

=SUM(COUNTIF(B2:B8,{"Blue","Green"}))

Then this array formula** in your first cell of choice:

=IF(ROWS(\$1:1)>\$C\$1,"",INDEX(\$A\$2:\$A\$8,SMALL(IF(\$B\$2:\$B\$8={"Blue","Green"},ROW(\$B\$2:\$B\$8)-MIN(ROW(\$B\$2:\$B\$8))+1),ROWS(\$1:1))))

Copy this formula down (though not the one in C1) until you start to get blanks for the results.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 Item Color X Y Blue and Green Items Grass Green Grass Water Blue Water Fire Red Sky Dirt Brown Go Light Sky Blue Car Go Light Green Car Blue

<TBODY>
</TBODY>

Select A1:E8.
Activate Insert | Table.

E2, control+shift+enter, not just enter, and copy down to E8...
Rich (BB code):
``````=IFERROR(INDEX(\$A\$2:\$A\$8,SMALL(IF(ISNUMBER(MATCH(\$B\$2:\$B\$8,{"Blue","Green"},0)),
ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),ROWS(\$E\$2:E2))),"")
``````

Thanks to both of you! They both work exactly how I want them to!

Thanks to both of you! They both work exactly how I want them to!

In the Table version, the target formula is automatically copied down for every addition of a record.

Replies
0
Views
596
Replies
2
Views
124
Replies
2
Views
724
Replies
4
Views
365
Replies
4
Views
194

1,196,507
Messages
6,015,604
Members
441,905
Latest member
Jean207

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back