How to Create Dynamic List in Excel Based on Criteria (Single and Multiple Criteria)

=FILTER Function to Create Dynamic List Based on Single Criteria

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


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.

A Dynamic List in Excel

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.

A Dynamic List in Excel

This is called a dynamic list.


Download Practice Workbook


How 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.

Data Set to Create Dynamic List in Excel Based on Criteria

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.


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(C4,0,0,COUNTA(C:C)-1,1),OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)

=FILTER Function to Create Dynamic List Based on Single Criteria

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

  • COUNTA(C:C) returns the number of rows in column C that are not blank. So COUNTA(C:C)-1 returns the number of rows that have values without the Column Header (Student Name in this example).
  • If you don’t have the Column Header, use COUNTA(C:C)
  • OFFSET(C4,0,0,COUNTA(C:C)-1,1) starts from cell C4 (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, the COUNTA(C:C)-1 formula will increase by 1 and the OFFSET function will include the student.
  •  Similarly, OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60 returns TRUE for all the marks that are greater than or equal to 60.
  • Finally, FILTER(OFFSET(C4,0,0,COUNTA(C:C)-1,1),OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60) returns a list of all the students who got marks more than 60.
  • If any new student is added to the data set, COUNTA(C:C)-1 increases by 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(C4,0,0,COUNTA(C:C)-1,2),OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)

Dynamic List in Excel Based on Single Criteria with FILTER Function


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(C4,0,0,COUNTA(C:C)-1,1),(OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B4,0,0,COUNTA(B:B)-1,1)<=200)) 

Dynamic Function in Excel Based on Multiple Criteria with FILTER Function

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

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

You change any value or add any new student in the data set, the list will adjust automatically.

Explanation of the Formula

  • Here we’ve multiplied two dynamic ranges of criteria, (OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B4,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 (Column B, C, and D in this example), change the first argument of the first OFFSET function to the first column (B4 in this example), and the fifth argument to the total number of columns (3 in this example).

=FILTER(OFFSET(B4,0,0,COUNTA(C:C)-1,3),(OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B4,0,0,COUNTA(B:B)-1,1)<=200)) 

Dynamic List in Excel Based on Multiple Criteria with FILTER Function


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 them, who use the older version of Excel, using the INDEX-MATCH, OFFSET, SMALL, IF, ROW, COUNTIF, and COUNTIFS functions of Excel.


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(C4,0,0,COUNTA(C:C)-1,1),MATCH(SMALL(IF(OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60,OFFSET(D4,0,0,COUNTA(D:D)-1,1),""),ROW(A1:INDIRECT("A"&COUNTIF(D:D,">=60")))),OFFSET(D4,0,0,COUNTA(D:D)-1,1),0),1)
[Array Formula. So don’t forget to press Ctrl + Shift + Enter.]

INDEX-MATCH Function to Make a Dynamic List in Excel Based on Single Criteria

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 the 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.

  • 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.
  • C4 and D4 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 IDs less than 200 will be;

=INDEX(OFFSET(C4,0,0,COUNTA(C:C)-1,1),MATCH(SMALL(IF((OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B4,0,0,COUNTA(B:B)-1,1)<=200),OFFSET(D4,0,0,COUNTA(D:D)-1,1),""),ROW(A1:INDIRECT("A"&COUNTIFS(B:B,"<=200",D:D,">=60")))),OFFSET(D4,0,0,COUNTA(D:D)-1,1),0),1)
[Array Formula. So don’t forget to press Ctrl + Shift + Enter.]

Dynamic List Based on Multiple Criteria with INDEX-MATCH Function

  • 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.
  • B4, C4, and D4 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: (OFFSET(D4,0,0,COUNTA(D:D)-1,1)>=60)*(OFFSET(B4,0,0,COUNTA(B:B)-1,1)<=200) .If you have more than two criteria, multiply accordingly.
  • I have again used the two criteria inside this COUNTIFS function: COUNTIFS(B:B,"<=200",D:D,">=60"). You use your ones accordingly.
  • Keep the rest of the formula unchanged and use it in your data set. You will get a dynamic list with multiple criteria.

3. Creating a Dynamic Drop Down List Based on Criteria

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.

Data Validation Tool in Excel

  • 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 (#)($F$4# in this example).

Data Validation Dialogue Box in Excel

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

Dynamic Drop-Down List Created in Excel


Conclusion

Using these methods, you can create a dynamic list based on single or multiple criteria in any data set. Do you have any questions? Feel free to ask us.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo