Dynamic List Excel (Complete Guideline)

Download Practice Workbook

Method 1 – Using FILTER and OFFSET Functions

Based on Single Criteria:

We will combine FILTER and OFFSET functions to make a dynamic list of the students whose average marks are greater than or equal to 60.

Enter the following formula:

=FILTER(OFFSET(C5,0,0,COUNTA(C:C)-1,1),OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)

Based on Multiple Criteria:

We will demonstrate how to make a dynamic list of the students who got marks more than or equal to 60, but whose IDs are less than or equal to 200.

Enter the following formula:

=FILTER(OFFSET(C5,0,0,COUNTA(C:C)-1,1),(OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B5,0,0,COUNTA(B:B)-1,1)<=200))

Method 2 – Using INDEX-MATCH with Other Functions

Based on Single Criteria:

We will use Excel’s INDEX-MATCH, OFFSET, SMALL, IF, ROW, COUNTIF, and COUNTIFS functions to create a dynamic list of the students who got more than or equal to 60.

Enter the following formula:

=INDEX(OFFSET(C5,0,0,COUNTA(C:C)-1,1),

MATCH(SMALL(IF(OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60,

OFFSET(D5,0,0,COUNTA(D:D)-1,1),""),

ROW(A1:INDIRECT("A"&COUNTIF(D:D,">=60")))),

OFFSET(D5,0,0,COUNTA(D:D)-1,1),0),1)

Based on Multiple Criteria:

We want to get the names of the students who got marks greater than or equal to 60 but have IDs less than 200.

Enter the following formula.

=INDEX(OFFSET(C5,0,0,COUNTA(C:C)-1,1),

MATCH(SMALL(IF((OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)*

(OFFSET(B5,0,0,COUNTA(B:B)-1,1)<=200),OFFSET(D5,0,0,COUNTA(D:D)-1,1),""),

ROW(A1:INDIRECT("A"&COUNTIFS(B:B,"<=200",D:D,">=60")))),

OFFSET(D5,0,0,COUNTA(D:D)-1,1),0),1)

How to Create a Dynamic Drop-Down List in Excel

Select any cell in your worksheet to create the dynamic drop-down list and go to Data > Data Validation > Data Validation under the Data Tools section.

You will get the Data Validation dialog box. Under the Allow Option, choose List. And under the Source option, enter the reference of the first cell where the list is in your worksheet and a hashtag (#)($E$5# in this example).

Click OK. You will get a drop-down list in your selected cell as shown below.

How to Create a Dynamic Unique List in Excel

Enter the following formula.

=UNIQUE(FILTER(E5:E17,(D5:D17>60)))

How to Create Dynamic Drop-Down List Using Excel OFFSET

Select any cell in your worksheet to create the dynamic drop-down list and go to Data > Data Validation > Data Validation under the Data Tools section.

From the Data Validation dialog box, select List from the drop-down.
In the source box, enter the following formula.

=OFFSET($C$4,0,0,COUNTA($C$4:$C$100),1)

Click OK. You will get a drop-down list in your selected cell like this.

Frequently Asked Questions

1. Can I update a dynamic list automatically in Excel?

Yes, a dynamically generated list in Excel updates automatically when the underlying data changes. This ensures that the range expands or shrinks dynamically without manual adjustment.

2. Can I use a dynamic list in combination with other Excel features, such as pivot tables or charts?

Yes, dynamic lists can be combined with other Excel features like pivot tables and charts. By referencing a dynamic list as the data source for these features, they will automatically update as the dynamic list adjusts.

3. Can I use a dynamic list as a source for a dropdown menu in Excel?

Yes, you can use dynamic lists as a source for a dropdown menu in Excel. By setting up data validation with a dynamic list as the source, the dropdown menu will update automatically.