Today I will be showing how to create a dynamic list based on single or multiple criteria in Excel.

**Download Practice Workbook**

**What Is a Dynamic List in Excel?**

A dynamic list is a list that is created from a data set and updates automatically when any value in the original data set is changed, or new values are added to the original data set.

In the given image, we have a list of the names of all the students who got marks greater than **60** in the examination.

Now if you change the marks of Jennifer Marlo from **68** to **58**, and add a new student called Ross Smith with marks **81** in the table, the list will adjust itself automatically.

This is called a dynamic list.

**3 Ways to Create Dynamic List in Excel Based on Criteria**

Here we’ve got a data set with the **Student IDs,** **Names,** and **Marks** of some students in a School called Sunflower kindergarten.

Our objective today is to make a dynamic list based on criteria from this data set. We will use both single and multiple criteria today.

**1. Using FILTER and OFFSET Functions (For New Versions of Excel)**

First of all, we will use a combination of the **FILTER**, **OFFSET**, and** COUNTA** functions of Excel.

The **FILTER** function is available in **Office 365 **only. So this is for only those who have an **Office 365** subscription.

**Case 1: Based on Single Criteria**

Let’s try to make a dynamic list of the students whose average marks are greater than or equal to **60**.

You can use this formula:

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

As you can see, we have got a list of all the students who got more than **60**.

And obviously, this is a dynamic list. You change any value in the data set or add any new value in the data set.

The list will adjust automatically.

**Explanation of the Formula:**

returns the number of rows in column C that are not blank. So`COUNTA(C:C)`

returns the number of rows that have values without the`COUNTA(C:C)-1`

**Column Header**(**Student Name**in this example).- If you don’t have the
**Column Header**, use`COUNTA(C:C)`

starts from cell`OFFSET(C5,0,0,COUNTA(C:C)-1,1)`

**C5**(Name of the first student) and returns a range of the names of all the students.- The
**OFFSET**function in combination with the**COUNTIF function**has been used to keep the formula dynamic. If one more student is added to the data set, theformula will increase by`COUNTA(C:C)-1`

**1**and the**OFFSET function**will include the student. - Similarly,
returns`OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60`

**TRUE**for all the marks that are greater than or equal to**60**. - Finally,
returns a list of all the students who got marks more than`FILTER(OFFSET(C5,0,0,COUNTA(C:C)-1,1),OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)`

**60**. - If any new student is added to the data set,
increases by`COUNTA(C:C)-1`

**1**, and the**FILTER**function refreshes the calculation including it. - Thus the formula always remains dynamic.

**Note: **

If you want to get the marks along with the names in the list, just change the fifth argument of the first **OFFSET function** from **1** to **2**.

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

**Case 2: Based on Multiple Criteria**

Let’s try multiple criteria this time.

We’ll try 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**.

You can use this 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))`

As you can see, we have got a list of all the students who got marks more than **60** and have **ID**s less than **200**.

And no need to tell, this is a dynamic list.

If you change any value or add any new student to the data set, the list will adjust automatically.

**Explanation of the Formula:**

- Here we’ve multiplied two dynamic ranges of criteria,
`(OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B5,0,0,COUNTA(B:B)-1,1)<=200)`

- If you have more than
**2**criteria, multiply all the ranges of criteria in the same way. - The rest is the same as the previous example (of single criteria). The
**OFFSET**function in combination with**the COUNTA function**has been used to keep the formula dynamic.

**Note:**

If you want to see all the columns in the list (**Columns B, C,** and **D** in this example), change the first argument of the first **OFFSET** function to the first column (**B5** in this example), and the fifth argument to the total number of columns (**3** in this example).

`=FILTER(OFFSET(B5,0,0,COUNTA(C:C)-1,3),(OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)*`

`(OFFSET(B5,0,0,COUNTA(B:B)-1,1)<=200))`

**Read More:** **How to Create Dynamic List From Table in Excel (5 Quick Ways)**

### 2. **Using INDEX-MATCH with Other Functions (For Old Versions)**

Those who don’t have an **Office 365** subscription can’t use the above formula.

I am showing a more complex way for those, who use the older version of Excel, using the **INDEX-MATCH, OFFSET, SMALL, IF, ROW, COUNTIF,** and **COUNTIFS** functions of Excel. Note that these formulas are **array formulas**. So, to apply them in older versions of Excel, you need to press **Ctrl+Shift+Enter** instead of just Enter.

**Case 1: Based on Single Criteria**

The formula to create a dynamic list of the students who got more than or equal to 60 will be:

`=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)`

As you can see, we have again got the names of all the students who got more than or equal to **60**.

This time we have got in ascending order of the numbers.

And yes, the list is dynamic. Add a new student to the data set, or change the marks of any student in the dataset.

The list will adjust itself automatically.

**Explanation of the Formula:**

- Here
**C:C**is the column from which we want to extract the contents of the list (**Student Name**in this example). You use your one. **D:D**is the column where the criterion lies (**Average Marks**in this example). You use your one.**C5**and**D5**are the cells from where my data have been started (just below the**Column Headers**). You use your one.**“>=60”**is my criterion (Greater than or equal to**60**in this example). You use your one.- Other than these few changes, keep the rest of the formula unchanged and use it in your data set. You will get a dynamic list according to your desired criterion.

**Case 2: Based on Multiple Criteria**

The **INDEX-MATCH** formula for the dynamic list based on multiple criteria is a bit more complex. Still, I am showing it.

The formula to get the names of the students who got marks greater than or equal to **60**, but have **ID**s less than **200** will be;

`=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)`

**Explanation of the Formula:**

- Here
**C:C**is the column from which we want to extract the contents of the list (**Student Name**in this example). You use your one. **B:B**and**D:D**are the columns where the criteria lie (**Student ID**and**Average Marks**in this example). You use your one.**B5, C5,**and**D5**are the cells from where my data have been started (just below the**Column Headers**). You use your one.- I have multiplied two criteria here:
.If you have more than two criteria, multiply accordingly.`(OFFSET(D5,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B5,0,0,COUNTA(B:B)-1,1)<=200)`

- I have again used the two criteria inside this
**COUNTIFS**function:You use your ones accordingly.`COUNTIFS(B:B,"<=200",D:D,">=60")`

. - Keep the rest of the formula unchanged and use it in your data set. You will get a dynamic list with multiple criteria.

**Read More: How to Make a Dynamic Data Validation List Using VBA in Excel**

**3. Create a Dynamic Drop Down List Based on Criteria Using Data Validation Tool**

Now we have created the dynamic list. If you want, you can **create a dynamic drop-down list** in any cell of your worksheet.

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

- You will get the
**Data Validation**dialogue 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 along with a**HashTag (#)**(**$E$5#**in this example).

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

**Read More: How to Create Dynamic Drop Down List Using VBA in Excel**

**How to Create a Dynamic Unique List in Excel Based on Criteria**

In this section, we will show how to create a unique list in Excel based on criteria. We will use a combination of **UNIQUE** and **FILTER** functions. We modified the dataset and added each student’s favorite games. Now, want to know the name of games removing duplicates with criteria. The criteria are average marks of the students must be greater than **60**.

**📌 ****Steps:**

- Put the formula based on the combination of
**UNIQUE**and**FILTER**functions on**Cell G5**.

`=UNIQUE(FILTER(E5:E25,(D5:D25>60)))`

We get a unique list based on criteria.

**Explanation of the Formula:**

**FILTER(E5:E25,(D5:D25>60)**

This filters the values of **Range E5:E25**, with a condition that average marks must be above **60**.

**Result: [Tennis, Volleyball, Rugby, Tennis, Football, Rugby, Rugby, Football]**

**UNIQUE(FILTER(E5:E25,(D5:D25>60)))**

This returns all the **unique values** from the previous result.

**Result: [Tennis, Volleyball, Rugby, Football]**

**Conclusion**

Using these methods, you can create a dynamic list based on single or multiple criteria in any data set in Excel. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.

Stops working when there’s two names with the same gradem returns in duplicate instead of showing the two names that have the same grade

Thanks for your feedback, we shall check the issue.

I have used your single criteria array formula for older excel versions.

It creates a dynamic list of results I expect with one exeption…

My C:C column contains unique values but the dynamic list is a list where many of the items are duplicated values… why is that?

My criteria column (D:D) does have duplicated values but that shouldn’t be an issue.

What could it be?

{=INDEX(OFFSET(imp_Voorraad!A2;0;0;COUNTIF(imp_Voorraad!A:A)-1;1);MATCH(SMALL(IF(OFFSET(imp_Voorraad!I2;0;0;COUNTIF(imp_Voorraad!I:I)-1;1)>=0;OFFSET(imp_Voorraad!I2;0;0;COUNTIF(imp_Voorraad!I:I)-1;1);””);ROW(A1:INDIRECT(“A”&COUNTIF(imp_Voorraad!I:I;”>=0″))));OFFSET(imp_Voorraad!I2;0;0;COUNTIF(imp_Voorraad!I:I)-1;1);0);1)}

Dear

Tijs Tijert,The used formula shouldn’t react this way. Also, I’m not sure what your data and values are that you used as criteria. Kindly email me your dataset to [email protected]. I’ll try my best to come up with a solution to your problem.

Regards

Maruf IslamHi Rifat,

using the single criteria array formula for older excel versions, I get duplicated values in my list, while the source column contains only unique items.

Here is the formula, modified to fit my situation.

{=INDEX(OFFSET(imp_Voorraad!A2;0;0;COUNTIF(imp_Voorraad!A:A)-1;1);MATCH(SMALL(IF(OFFSET(imp_Voorraad!I2;0;0;COUNTIF(imp_Voorraad!I:I)-1;1)>=0;OFFSET(imp_Voorraad!I2;0;0;COUNTIF(imp_Voorraad!I:I)-1;1);””);ROW(A1:INDIRECT(“A”&COUNTIF(imp_Voorraad!I:I;”>=0″))));OFFSET(imp_Voorraad!I2;0;0;COUNTIF(imp_Voorraad!I:I)-1;1);0);1)}

The only difference being that the formula is on another sheet within the workbook, than the source data.

Can you please explain why that is? Because I’ve been staring at this for a while and don’t see it.

Hi all,

I would be great if someone can provide this group with a solution when the values in the criteria columns are repeated (I am trying to use this formula from a list with multiple criterias and both lists have repeated values). The key is that the combination of the two criterias are unique

The other issue I found is that the countifs formula only works if I typed the condition under brakets “”, and the formula doesnt work if I reference to the value to a cell.

If you use the ‘Unique’ formula around the complete formula, it returns distinct values only.

Eg: =UNIQUE(FILTER(OFFSET(REF!$U$2,0,0,COUNTA(REF!$U:$U)-1,1),(OFFSET(REF!$R$2,0,0,COUNTA(REF!$R:$R)-1,1)=EXAMPLE!$C$7)*(OFFSET(REF!$S$2,0,0,COUNTA(REF!$S:$S)-1,1)=EXAMPLE!$C$8)*(OFFSET(REF!$T$2,0,0,COUNTA(REF!$T:$T)-1,1)=EXAMPLE!$C$9),””))

Thanks for your input, Josh. You are absolutely right.

Regards,

Md. Shamim Reza (ExcelDemy Team)

How do you write less than 60 but greater than 40 in this formula

=FILTER(OFFSET(C4,0,0,COUNTA(C:C)-1,2),OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)

How would you do less than 60 but greater than 40 ?

For the first filter offset?

=FILTER(OFFSET(C4,0,0,COUNTA(C:C)-1,2),OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)

Thanks

Robbie