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

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Create Dynamic List From Table in Excel

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

**Formula Breakdown**

**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 theargument,*reference***0**is theargument which tells the function how many rows to move from the initial reference*rows***$E$5**and**0**represents theargument, which specifies the column from starting point. Next, the*columns***COUNTA(E:E)-1**is the optionalargument, which counts the number of non-blank cells in the range, while the*height***1**is the optionalargument.*width***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 theargument that refers to the*range**“Total Sales”*, while the**“>=50”**indicates theargument that returns the count of the matched value. We have taken the entire*criteria***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 theargument.*ref_text***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 theargument that selects the*logical_test**“Total Sales”*values above*“$50”*. If this condition holds**TRUE**then the function returns**OFFSET($E$5,0,0,COUNTA(E:E)-1,1)**(argument) otherwise it returns*value_if_true***“” (Blank)**(argument).*value_if_false***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 theargument while*array***{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 theargument while*lookup_value***{64;69;40;128;142;180;114;110;66;68;78;123;82;272}**represents theargument from where the value is matched. Lastly,*lookup_array***0**is the optionalargument, which indicates the*match_type***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 theargument,*reference***0**is theargument which tells the function how many rows to move from the initial reference*rows***$E$5**and**0**represents theargument which specifies the column from starting point. Next, the*columns***COUNTA(B:B)-1**is the optionalargument, while the*height***1**is the optionalargument.*width***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 theargument that are the*array**“Product”*names. Next,**{10;2;11;13;8;7;12;4;5;6;14}**is theargument that indicates the row location. Lastly,*row_num***1**is the optionalargument that points to the column location.*column_num*

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 (Single and Multiple 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)`

**Formula Breakdown**

**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 theargument, while*array***OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80**is theargument. Lastly, the*include***OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100**is the optionalargument. The function returns the*if_empty**“Product Names”*that match both these criteria.**Output → {“Cereal”;”Pretzels”;”Meat”}**

**Read More:** **How to Create a Dynamic Top 10 List in Excel (8 Methods)**

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

**Formula Breakdown**

**INDEX(B4:E18,SEQUENCE(5),SEQUENCE(1,4))****SEQUENCE****(5) →**returns a sequence of numbers. Here,**5**is theargument.*rows***Output → {1;2;3;4;5}**

**SEQUENCE(1,4) →**here,**1**is theargument while*rows***4**is thecolumns argument.*optional***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 theargument representing the dataset while*array***{1;2;3;4;5}**is theargument that returns the first*row_num***5**rows and**{1;2;3;4}**is the optionalargument that indicates the location of the columns.*column_num*

### 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>`

**Formula Breakdown**

**VLOOKUP(@C:C,$G$5:$H$13,2,FALSE) →**Here,**@C:C**(argument) is mapped from the*lookup_value***$G$5:$H$13**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***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 Make a Dynamic Data Validation List Using VBA in Excel**

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

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