Nazmus Sakib
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.
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.
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)
- 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.
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)
- 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.
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.
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")
- 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.
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"))
- 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.
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"))
- 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.
