3 Ways to Create Dynamic List Based on Criteria in Excel

Sometimes we need to create a Dynamic List in Excel to perform various operations or just to show the values that match the conditions. In this article, we will learn how to apply formulas to create Dynamic List based on criteria in Excel in 3 convenient approaches.


📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.


What Is Dynamic List?

A dynamic list is a list that satisfies particular or multiple conditions. Furthermore, if you change a value in the source list, the change will be automatically updated on the dynamic list if it satisfies the implemented criteria.


Learn to Create Dynamic List Based on Criteria in Excel with These 3 Easy Approaches

In this article, we will learn how to create a dynamic list based on criteria in Excel. We will use the following dataset to achieve our goal.


Approach

1. Implementing INDEX, OFFSET, and MATCH Functions

We will discuss how to create dynamic list based on criteria in Excel using the combination of INDEX, MATCH, and other functions.

Approach 1.1

1.1 Specific Criterion

For a particular condition, let’s see how we merge the INDEX and MATCH functions. Here, we will create a dynamic list where the Product price is greater than $1500.

  ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select cell F6 and Enter the formula below:

=INDEX(OFFSET(B6,0,0,COUNTA(B:B)-2,1),MATCH(SMALL(IF(OFFSET(D6,0,0,COUNTA(D:D)-1,1)>1500,OFFSET(D6,0,0,COUNTA(D:D)-1,1),""),ROW(B1:INDIRECT("B"&COUNTIF(D:D,">1500")))),OFFSET(D6,0,0,COUNTA(D:D)-1,1),0),1)

how to create dynamic list based on single criteria using index, offset and match functions in excel

  • After that, you will see a dynamic from cells F6:F10. A thin blue border will appear around the list if you select any of the cells from the dynamic list. If you cross-check with the source list, you will find the dynamic list containing the Product names that satisfy the condition.
  • Then, if we change the Price of the iMac 24″ to $1699, it will automatically appear on the dynamic list.

🔨 Formula Breakdown

INDEX(OFFSET(B6,0,0,COUNTA(B:B)-2,1),MATCH(SMALL(IF(OFFSET(D6,0,0,COUNTA(D:D)-1,1)>=599,OFFSET(D6,0,0,COUNTA(D:D)-1,1),””),ROW(B1:INDIRECT(“B”&COUNTIF(D:D,”>=599″)))),OFFSET(D6,0,0,COUNTA(D:D)-1,1),0),1)

👉  Let’s see what the IF function will return.

IF(OFFSET(D6,0,0,COUNTA(D:D)-1,1)>=599,OFFSET(D6,0,0,COUNTA(D:D)-1,1),””)

👉  First, the COUNTA function will count non-empty cells from column D. We subtracted 1 because we don’t want the header cell to be counted. Here, the OFFSET function will return an array from column D where the IF function finds values greater than $1500. Otherwise, the IF function will return a blank cell.

👉  The array returned by the IF and the OFFSET functions will work as an array for the SMALL function. The SMALL function will return the k th smallest value from the array.

ROW(B1:INDIRECT(“B”&COUNTIF(D:D,”>=1500″)

👉  This will work as the k th value for the SMALL function. First, the COUNTIF function will return the number of cells containing values greater than 1500 from column D. The INDIRECT function will concatenate B with value eg. B5 if the COUNTIF function finds 5 cells accommodating values greater than $1500. The ROW function will return the row number in this case it will be 1.

👉  Thus the SMALL function will return an array of values greater than $1500.

👉  The MATCH function will match those values from column D.

👉  The INDEX function will use the MATCH function returnees as its row number and will return the corresponding value from column B as the final list.

Note:

  • The dynamic list will appear in ascending order if you use this formula.
  • The condition has to be a numeric value. Otherwise, the SMALL function will return the #NUM! error.
  • Also, the column where you want to apply the condition has to contain unique Otherwise, the formula will always keep returning the first duplicate value.
  • It is recommended to use the second method to create dynamic lists if you have duplicates in your source list.

Approach 1.2

1.2 Multiple Criteria

Now, we will demonstrate how to create a dynamic list where the Product Price is greater than $1500 and the Product is Delivered using the INDEX, and MATCH nested functions.

  ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Enter the following formula in cell F6.

=INDEX(OFFSET(B6,0,0,COUNTA(B:B)-2,1),MATCH(SMALL(IF((OFFSET(D6,0,0,COUNTA(D:D)-1,1)>1500)*((OFFSET(C6,0,0,COUNTA(C:C)-1,1)="Delivered")),OFFSET(D6,0,0,COUNTA(D:D)-1,1),""),ROW(B1:INDIRECT("B"&COUNTIFS(D:D,">1500",C:C,"Delivered")))),OFFSET(D6,0,0,COUNTA(D:D)-1,1),0),1)

how to create dynamic list based on multiple criteria using index, offset and match functions in excel

  • Done! Your dynamic list is ready.

🔨 Formula Breakdown

INDEX(OFFSET(B6,0,0,COUNTA(B:B)-2,1),MATCH(SMALL(IF((OFFSET(D6,0,0,COUNTA(D:D)-1,1)>=599)*((OFFSET(C6,0,0,COUNTA(C:C)-1,1)=”Delivered”)),OFFSET(D6,0,0,COUNTA(D:D)-1,1),””),ROW(B1:INDIRECT(“B”&COUNTIFS(D:D,”>=599″,C:C,”Delivered”)))),OFFSET(D6,0,0,COUNTA(D:D)-1,1),0),1)

👉  Here, we used an almost identical formula as the previous method.

👉  We just added another condition using the AND (*)  logic inside the IF function. Now, the IF function will return where it finds Delivered in column C and the values greater than $1500 in column D.

👉  The COUNTIFS function will do the same for the INDIRECT function as well. The rest of the formula remains the same as before.


Approach

2. Applying FILTER, OFFSET, and COUNTA Functions

If your source list contains duplicate values where you want to apply the condition, you should use the following approaches. We will use the FILTER function in this approach.

Approach 2.1

2.1 Specific Criterion

Just like the previous approach, we will create a dynamic list where the Product Price is greater than $1500, albeit we will use the combination of the FILTER, OFFSET, COUNTA, and other functions.

  ⬇️⬇️ STEPS ⬇️⬇️

  • Enter the following formula in cell F6.

=FILTER(OFFSET(B6,0,0,COUNTA(B:B)-2,2),OFFSET(D6,0,0,COUNTA(D:D)-1,1)="Delivered")

how to create dynamic list based on single criteria using filter, offset and counta functions in excel

  • Done! The dynamic list is ready. The curly brackets appear because this is an array formula. We showed the first two columns here tweaking the column number to 2 in the OFFSET function.

🔨 Formula Breakdown

FILTER(OFFSET(B6,0,0,COUNTA(B:B)-2,2),OFFSET(D6,0,0,COUNTA(D:D)-1,1)=”Delivered”)

👉  The COUNTA function will count non-empty cells from columns B and D. We subtracted 2 from the first COUNTA function because we don’t want the title and the header cells to be counted. Similarly, we don’t want the header cell to be present in the count for column D.

👉  The FILTER function will filter Product names from Column B where the second OFFSET function finds Delivered in column D.

Note:

The FILTER function is only available in OFFICE 365.


Approach 2.2

2.2 Multiple Criteria

We will create a dynamic list where the Product price is greater than $1500 and the product is Delivered.

  ⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F6. Enter the formula below:

=FILTER(OFFSET(B6,0,0,COUNTA(B:B)-2,1),(OFFSET(D6,0,0,COUNTA(D:D)-1,1)="Delivered")*(OFFSET(C6,0,0,COUNTA(C:C)-1,1)="Regular"))

how to create dynamic list based on multiple criteria using filter, offset and counta functions in excel

  • Voila! A dynamic list is created based on the two given conditions.

🔨 Formula Breakdown

FILTER(OFFSET(B6,0,0,COUNTA(B:B)-2,1),(OFFSET(D6,0,0,COUNTA(D:D)-1,1)=”Delivered”)*(OFFSET(C6,0,0,COUNTA(C:C)-1,1)=”Regular”))

👉  This formula is similar to the previous formula. The only change we made is that we added a new Condition.

👉  After that, If the second OFFSET function finds Delivered in column D and Regular in column C, the first one will return the corresponding Products from column B.


Approach

3. Employing UNIQUE Function for Duplicate Values

Now we will use the UNIQUE function to create a dynamic list without duplicate values.

  ⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F6 and Enter the following formula:

=UNIQUE(FILTER(OFFSET(B6,0,0,COUNTA(B:B)-2,2),OFFSET(D6,0,0,COUNTA(D:D)-1,1)="Delivered"))

how to create unique dynamic list based on criteria using unique function in excel

  • Then, this formula will return the Product names along with the Delivery Moreover, it will only return unique values.

🔨 Formula Breakdown

UNIQUE(FILTER(OFFSET(B6,0,0,COUNTA(B:B)-2,2),OFFSET(D6,0,0,COUNTA(D:D)-1,1)=”Delivered”))

👉  The UNIQUE function will return only the unique values. Here, Product MacBook Pro 16” and Delivery Type Rapid is present twice in the source list. The UNIQUE function will return it only once.

👉  Although Mac Pro appears twice in the dynamic list, it has two different delivery types.


📝 Takeaways from This Article

📌  First of all, we learned how to create a dynamic list using the INDEX, and MATCH functions for one condition.

📌  Secondly, we used the INDEX, and MATCH functions for two conditions to return a dynamic list.

📌  Later, we discussed a better way to create a dynamic list using the FILTER, and OFFSET functions for one condition.

📌  Afterward, we demonstrated how to create a dynamic list using the FILTER, and OFFSET functions based on multiple conditions.

📌  Then, we created a unique dynamic list using the UNIQUE function in our final method.


Conclusion

That concludes the discussion for today. These are some convenient methods to create dynamic lists in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.

(Visited 145 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo