Certainly, Microsoft Excel is a popular and useful tool for analyzing large sets of data. In general, lists are made by extracting entries from a dataset. Now, lists that update automatically are known as dynamic lists. In this article, we’ll describe 5 easy ways how to create a dynamic list from a table in Excel.
Create Dynamic List From Table in Excel: 5 Quick Ways
First and foremost, let’s assume the Sales Dataset containing the “Product”, “Quantity”, “Unit Price” and “Total Sales” columns shown in the B4:E18 cells. Here, we want to generate a dynamic list from a table in Excel using the Table feature and combining functions. Henceforth, without further delay, let’s glance at each method in detail and with the necessary illustrations.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Creating Dynamic List Based on Cell Value
In the first place, let’s construct a dynamic list based on a given criterion. Here, we’ll use the INDEX, MATCH, OFFSET, COUNTA, SMALL, IF, INDIRECT, COUNTIF, and ROW functions to obtain the “Product” names whose “Total Sales” value exceeds “$50”.
📌 Steps:
- Initially, enter the G5 cell >> enter the formula given below >> click the ENTER button.
=INDEX(OFFSET($B$5,0,0,COUNTA(B:B)-1,1),MATCH(SMALL(IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),""),ROW(A3:INDIRECT("A"&COUNTIF(E:E,">=50")))),OFFSET($E$5,0,0,COUNTA(E:E)-1,1),0),1)
- OFFSET($E$5,0,0,COUNTA(E:E)-1,1) → returns a range of cells from the specified rows and columns. Where $E$5 is the reference argument, 0 is the rows argument which tells the function how many rows to move from the initial reference $E$5 and 0 represents the columns argument, which specifies the column from starting point. Next, the COUNTA(E:E)-1 is the optional height argument, which counts the number of non-blank cells in the range, while the 1 is the optional width argument.
- Output → {64;69;40;128;142;180;114;110;66;68;78;123;82;272}
- COUNTIF(E:E,”>=50″) → counts the number of cells within a range that meet the given condition. Here, the E:E cells represent the range argument that refers to the “Total Sales”, while the “>=50” indicates the criteria argument that returns the count of the matched value. We have taken the entire E column (E:E) instead of E5:E18 cells for making the list dynamic.
- Output → 13
- INDIRECT(“A”&COUNTIF(E:E,”>=50″)) → returns the reference specified by a text string. Here, “A”&COUNTIF(E:E,”>=50″) is the ref_text argument.
- Output → 0
- ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″))) → returns the serial number of the row.
- Output → {3;4;5;6;7;8;9;10;11;12;13}
- IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),””) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, $E$5,0,0,COUNTA(E:E)-1,1)>=50 is the logical_test argument that selects the “Total Sales” values above “$50”. If this condition holds TRUE then the function returns OFFSET($E$5,0,0,COUNTA(E:E)-1,1) (value_if_true argument) otherwise it returns “” (Blank) (value_if_false argument).
- Output → {64;69;””;128;142;180;114;110;66;68;78;123;82;272}
- SMALL(IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),””),ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))) → becomes
- SMALL({64;69;””;128;142;180;114;110;66;68;78;123;82;272},{3;4;5;6;7;8;9;10;11;12;13}) → returns the kth smallest value in data set. Here, {64;69;””;128;142;180;114;110;66;68;78;123;82;272} is the array argument while {3;4;5;6;7;8;9;10;11;12;13} is the k argument.
- Output → {68;69;78;82;110;114;123;128;142;180;272}
- MATCH(SMALL(IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),””),ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))),OFFSET($E$5,0,0,COUNTA(E:E)-1,1),0) → becomes
- MATCH({68;69;78;82;110;114;123;128;142;180;272},{64;69;40;128;142;180;114;110;66;68;78;123;82;272},0) → returns the relative position of an item in an array matching the given value. Here, {68;69;78;82;110;114;123;128;142;180;272} is the lookup_value argument while {64;69;40;128;142;180;114;110;66;68;78;123;82;272} represents the lookup_array argument from where the value is matched. Lastly, 0 is the optional match_type argument, which indicates the Exact match criteria.
- Output → {10;2;11;13;8;7;12;4;5;6;14}
- OFFSET($B$5,0,0,COUNTA(B:B)-1,1) → here $B$5 is the reference argument, 0 is the rows argument which tells the function how many rows to move from the initial reference $E$5 and 0 represents the columns argument which specifies the column from starting point. Next, the COUNTA(B:B)-1 is the optional height argument, while the 1 is the optional width argument.
- Output → {“Chocolate Chip”;”Whole Wheat”;”Arrowroot”;”Rice”;”Carrot”;”Cereal”;”Oatmeal Raisin”;”Mixed Fruit”;”Pretzels”;”Jelly”;”Bran”;”Oatmeal Raw”;”Carrot Salad”;”Meat”;0;”Condition”}
- INDEX(OFFSET($B$5,0,0,COUNTA(B:B)-1,1),MATCH(SMALL(IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),””),ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))),OFFSET($E$5,0,0,COUNTA(E:E)-1,1),0),1) → becomes
- INDEX({“Chocolate Chip”;”Whole Wheat”;”Arrowroot”;”Rice”;”Carrot”;”Cereal”;”Oatmeal Raisin”;”Mixed Fruit”;”Pretzels”;”Jelly”;”Bran”;”Oatmeal Raw”;”Carrot Salad”;”Meat”;0;”Condition”},{10;2;11;13;8;7;12;4;5;6;14},1) → returns a value at the intersection of a row and column in a given range. In this expression, the ({“Chocolate Chip”;”Whole Wheat”;”Arrowroot”;”Rice”;”Carrot”;”Cereal”;”Oatmeal Raisin”;”Mixed Fruit”;”Pretzels”;”Jelly”;”Bran”;”Oatmeal Raw”;”Carrot Salad”;”Meat”;0;”Condition”} is the array argument that are the “Product” names. Next, {10;2;11;13;8;7;12;4;5;6;14} is the row_num argument that indicates the row location. Lastly, 1 is the optional column_num argument that points to the column location.
Boom! That is how simple it is to create a dynamic list from table in Excel.
Read More: How to Create Dynamic List in Excel Based on Criteria
2. Generating Dynamic List with Multiple Criteria
Alternatively, we can specify multiple criteria to generate a dynamic list using the FILTER, OFFSET, and COUNTA functions. For example, we want to extract the “Product” names which sold over “80” units in “Quantity” and generated “Total Sales” greater than “$100”.
📌 Steps:
- First of all, move to the G10 cell >> copy and paste the following expression >> hit the ENTER key.
=FILTER(OFFSET($B$5,0,0,COUNTA(E:E)-1,1),OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80,OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100)
- OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80 → here the function returns TRUE or FALSE for the “Quantity” values greater than “80”.
- Output → {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}
- OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100 → here the function returns TRUE or FALSE for the “Total Sales” greater than “$100”.
- Output → {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
- FILTER(OFFSET($B$5,0,0,COUNTA(E:E)-1,1),OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80,OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100) → filter a range or array. Here, OFFSET($B$5,0,0,COUNTA(E:E)-1,1) is the array argument, while OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80 is the include argument. Lastly, the OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100 is the optional if_empty argument. The function returns the “Product Names” that match both these criteria.
- Output → {“Cereal”;”Pretzels”;”Meat”}
Read More: How to Create a Dynamic Top 10 List in Excel
3. Utilizing Data Validation Feature with Helper Column
Besides, if writing complex expressions and combining functions is not up your alley, then our third method is the answer to your prayers! In this scenario, we’ll create a helper column and employ the Data Validation feature of Excel to build a dynamic list. So, just follow the steps shown below.
📌 Steps:
- At the very beginning, choose the B5:C13 cells >> click the CTRL + T keys to create Excel Table >> press OK.
- Then, jump to the H6 cell >> use the UNIQUE function to return the unique values in the B6:B13 range.
=UNIQUE(B6:B13)
Here, the B6:B13 cells represent the “Item” column.
- Following this, choose the E6 cell >> navigate to the Data tab >> click the Data Validation option.
In turn, this opens the Data Validation window.
- At this point, in the Allow field, choose the List option >> enter the cell reference below in the Source field >> hit OK.
$H$6#
For instance, the H6 cell points to “Item” in the “Helper Column” while the # (Hashtag) sign indicates the Spill Range.
- Later, click the Down arrow >> select an “Item” from the list, here we’ve chosen “Car”.
- Not long after, go to the I6 cell >> insert the FILTER function to filter the array. Here, the “Country” corresponding to each “Item” is shown.
=FILTER(C6:C13,B6:B13=E6)
On this occasion, the C6:C13 and B6:B13 ranges point to the “Item” and “Country” columns, while the E6 cell refers to the “Item” chosen from the Data Validation drop-down.
- Likewise, follow similar steps to insert another drop-down in the F6 cell.
$I$6#
In this case, the I6 cell represents the “Country” in the “Helper Column” and the # (Hashtag) sign refers to the Spill Range.
Lastly, adding new data automatically updates the dynamic drop-down list, as shown in the screenshot below.
4. Making Dynamic List Without Creating Helper Column
Conversely, we can produce a dynamic list without a helper column with the help of the INDEX and SEQUENCE functions. For example, we’ll extract the first 5 rows and the 4 columns from the dataset. Therefore, let’s see it in action.
📌 Steps:
- To begin with, type the formula into the B21 cell >> click on ENTER.
=INDEX(B4:E18,SEQUENCE(5),SEQUENCE(1,4))
- INDEX(B4:E18,SEQUENCE(5),SEQUENCE(1,4))
- SEQUENCE(5) → returns a sequence of numbers. Here, 5 is the rows argument.
- Output → {1;2;3;4;5}
- SEQUENCE(1,4) → here, 1 is the rows argument while 4 is the optional columns argument.
- Output → {1;2;3;4}
- INDEX(B4:E18,SEQUENCE(5),SEQUENCE(1,4)) → becomes
- INDEX(B4:E18,{1;2;3;4;5},{1;2;3;4}) → returns a value at the intersection of a row and column in a given range. In this expression, the B4:E18 is the array argument representing the dataset while {1;2;3;4;5} is the row_num argument that returns the first 5 rows and {1;2;3;4} is the optional column_num argument that indicates the location of the columns.
Read More: How to Make Dynamic Drop Down List from Another Sheet in Excel
5. Using Excel Table and VLOOKUP Function
Last but not least, we can implement the versatile VLOOKUP function in conjunction with the Excel Table to automatically look up the corresponding data in a dynamic list.
📌 Steps:
- To start with, proceed to the E5 cell >> enter the equation into the Formula Bar >> press the ENTER key >> drag the Fill Handle tool to copy the formula into the cells below.
<span style="font-size: 14pt;">=VLOOKUP(@C:C,$G$5:$H$13,2,FALSE)</span>
- VLOOKUP(@C:C,$G$5:$H$13,2,FALSE) → Here, @C:C ( lookup_value argument) is mapped from the $G$5:$H$13 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → PC
- Next, select the G4:H12 array >> press the CTRL + T shortcut keys to insert Excel Table >> press OK.
- Afterward, we’ll add a new “Item” and its “Item Code” as shown below.
- Eventually, when we enter new data into the dataset, Excel dynamically inserts the “Item Code”.
Read More: How to Create Dynamic Drop Down List Using Excel OFFSET
Practice Section
We have provided a Practice section on the right side of each sheet, so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
To sum up, we hope this tutorial has provided you with helpful knowledge on how to create a dynamic list from table in Excel. Now, we recommend you apply all this know-how to the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.
<< Go Back to Dynamic List Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
