We have a dataset of several people from different locations along with their vehicles. Using this data, we will form a list based on criteria.

### Method 1 – Using the INDEX-SMALL Combination to Generate a List

**Steps:**

- Select the
**F5Â**cell and insert:

`=IFERROR(INDEX($B$5:$B$12,SMALL(IF($C$5:$C$12=F$4,ROW($B$5:$B$12)),ROW(1:1))-4,1),"")`

- Press
**Enter.**

- We will get the name associated with the region.
**AutoFill**the values to the right for the rest of the regions.

- Drag the Fill Handle down from the
**F5Â**cell.

- Drag the Fill Handle to the right to
**AutoFill**the values for the rest of the regions.

**Formula Breakdown**

**IF($C$5:$C$12=F$4,ROW($B$5:$B$12)):**Â The**ROW($B$5:$B$12)**returns an array of values of rows,Â**{5;6;7;8;9;10;11;12}**. Again,**$C$5:$C$12=F$4Â**expression returns an array of**TRUE**and**FALSE**Â the**{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}**. So, finally theÂ**IFÂ**function returns the values from the**{5;6;7;8;9;10;11;12}Â**array which matches theÂ**TRUEÂ**values from the**{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}Â**array and keeps the rest of the valuesÂ**FALSE.**- Output :
**{5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.**

- Output :
**SMALL({5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},ROW(1:1)):**It becomes**SMALL({5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1}**). The**SMALLÂ**function returns the 1st smallest value from the**{5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}Â**array. So, the output will beÂ**{5}.**- Output:Â
**{5}.**

- Output:Â
**INDEX($B$5:$B$12,{5}-4,1):**The**INDEX($B$5:$B$12,{5}-4,1)Â**becomes**INDEX($B$5:$B$12,1,1).Â**TheÂ**INDEX**function will return the first value in the**$B$5:$B$12Â**range which isÂ**Mac.**- Output:Â
**“Mac”**

- Output:Â
**IFERROR(INDEX($B$5:$B$12,SMALL(IF($C$5:$C$12=F$4,ROW($B$5:$B$12)),ROW(1:1))-4,1),””):**The entire formula becomes**IFERROR(“Mac”,””).Â**TheÂ**IFERRORÂ**function will returnÂ**Mac.**- Output:Â
**Mac**

- Output:Â

**Read More: **How to Make a List within a Cell in Excel

### Method 2 – Using the AGGREGATE Function to Generate a List

**Steps:**

- Choose the
**F5Â**cell and insert:

`=IFERROR(INDEX($B$5:$B$12,AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)),1),"")`

- Hit
**Enter.**

**AutoFill**the values for the remaining regions.

- You can drag the fill handle down to get the next values.

- Drag to the right to
**AutoFill**the values for the rest of the regions.

**Formula Breakdown**

**IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4):**The formula becomes**IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{5;6;7;8;9;10;11;12}).**After deducting 4 from each values of**{5;6;7;8;9;10;11;12}**the set becomes**{1;2;3;4;5;6;7;8}.Â**Finally, theÂ**IFÂ**function will return**{1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.**- Output:
**{1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}**

- Output:
**AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)):**The numberÂ**15Â**in theÂ**AGGREGATEÂ**denotes theÂ**SMALLÂ**function. So the expression becomesÂ**SMALL({1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1}**). And the expression will return**{1}**.- Output:
**{1}**

- Output:
**INDEX($B$5:$B$12,AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)),1):**It will become**INDEX($B$5:$B$12,{1},1).Â**TheÂ**INDEXÂ**function will return the first value of the**$B$5:$B$12Â**range which is**Mac.**- Output:
**“Mac”**

- Output:
**IFERROR(INDEX($B$5:$B$12,AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)),1),””):**Finally, this expression will become**IFERROR(“Mac”,””).Â**Thus theÂ**IFERRORÂ**function will returnÂ**Mac.**- Output:Â
**Mac**

- Output:Â

**Read More: **How to Create a Contact List in Excel

### Method 3 – Generate Unique List Using INDEX-MATCH-COUNTIF

**Steps:**

- Select the
**F5Â**cell and insert the following:

`=IFERROR(INDEX($B$5:$B$12, MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F5:F5, $B$5:$B$12), ""), 0)),"")`

- Press the
**Enter**button.

- Drag the Fill Handle to the right to
**AutoFill**the values.

- Drag the Fill Handle down from the
**F5Â**cell to**AutoFill**forÂ**New York.**

- Drag the Fill Handle to the right to
**AutoFill**the values for the remaining regions.

**Formula Breakdown**

**COUNTIF(F4:F4, $B$5:$B$12):**The expression becomes**{0;0;0;0;0;0;0;0}.Â**As theÂ**COUNTIFÂ**function can not find any of the values in theÂ**$B$5:$B$12**range from the**F4:F4.**- Output:
**{0;0;0;0;0;0;0;0}**

- Output:
**IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12), “”):Â**The expression becomes**{0;0;””;””;””;””;””;””}.Â**As the**F$4=$C$5:$C$12Â**returns an array like this-**{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.Â**- Output:Â
**{0;0;””;””;””;””;””;””}.**

- Output:Â
**MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12)):**This expression becomes**MATCH(0, {0;0;””;””;””;””;””;””}).Â**The**MATCHÂ**function matchesÂ**0Â**with the first value of the**{0;0;””;””;””;””;””;””}Â**set and will return**1.**- Output:Â
**1**

- Output:Â
**INDEX($B$5:$B$12, MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12), “”), 0)):**The expressioin will become**INDEX($B$5:$B$12,1)**. TheÂ**INDEXÂ**function will return the first value of the**$B$5:$B$12Â**range that isÂ**Mac.**- Output:Â
**“Mac”.**

- Output:Â
**IFERROR(INDEX($B$5:$B$12, MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12), “”), 0)),””):**This expression becomes**IFERROR(“Mac”,””).Â**So, theÂ**IFERRORÂ**function will returnÂ**Mac.**- Output:Â
**Mac**

- Output:Â

**Read More: **Create a Unique List in Excel Based on Criteria

### Method 4 – Using the FILTER Function to Generate a List Based on Criteria

Only works on Excel 365.

**Steps:**

- Choose the
**F5Â**cell and enter the following:

`=FILTER($B$5:$B$12,$C$5:$C$12=F$4)`

- Hit the
**Enter**button.

**AutoFill**the values for the rest of the regions.

### Method 5 – Applying the TEXTJOIN Function

**Steps:**

- Select the
**G5Â**cell and enter the following:

`=TEXTJOIN(", ",TRUE,IF(F5=$C$5:C12,$B$5:$B$12,""))`

- Press
**Enter.**

- We will get the names for that particular region separated by comma.
- Drag the AutoFill down to
**AutoFill**the values.

**Download the Practice Workbook**

## Further Readings

- How to Make a Comma Separated List in Excel
- How to Make a Price List in Excel
- How to Make a To Do List in Excel
- How to Make a Numbered List in Excel

**<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel**

Not a single one of these formulas work. I have used them all in the practice workbook, outlined exactly as you present and none of them work.

Did you download the working file? We use Microsoft 365 for making our Excel tutorials. Please download the workbook and let me know whether the system works or not.

Thanks.

For this formula, =IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), “”), 0)),””) is it possible to create the list based on multiple criteria? Eg, names by region based on Motor Vehicles

Thanks for your query.

Yes, it is possible to apply multiple criteria. Not sure how your data looks, but according to your query, I have tried to reorganize it as follows to categorize names by region based on Vehicle.

=INDEX(B5:B15,MATCH(1,(B18=$D$5:$D$15) * (C18=$C$5:$C$15),0))Is it possible to put all the names in one cell?

I have the use the following formula for this case.

=TEXTJOIN(“, “,TRUE,IF(F5=$C$5:$C$15,$B$5:$B$15,””))Wow. Very handy. And very nicely done! kudos

Thanks for your appreciation. It means a lot.

Can I use this if I want to search row 23 for the value “2” in order to obtain the information from Row 6? I would want it to provide a list just as it is above too so no repeat values.

How do you modify the formula if your ranges does not start on row 1?

For example your data start at row 6. Then none of the formulas is working properly.

Unfortunately for some strange reasons, the formulas with the INDEX function don’t behave properly while starting from any other row but row 1. But luckily you have some other alternatives like the FILTER function in case your dataset start from row6

Can you modify the formula for a row search. The value I am searching for is “2” in row 23. The value I want returned is in Row 6. I would want it to create a list just as displayed above each time a 2 is found in row 23 of the values in row 6. Is this possible?

You can apply the INDEX – MATCH functions combination to find out whether the value is matching with “2 ” or not in row 23 and then, use the INDIRECT function to retrieve the matched value with the value in row 6.

Hi,

When I reference criteria from another worksheet, it generates an error – any thoughts?

Hi

RAV,It would be great if you share your Excel workbook. Because this formula works fine with criteria from other worksheets in our workbook.

See the screenshot below.

In

Sheet3, we inserted the formula and gave all the arguments fromSheet2.`=IFERROR(INDEX('INDEX - SMALL Formula'!$B$2:$B$12,SMALL(IF('INDEX - SMALL Formula'!$C$2:$C$12=G$4,ROW('INDEX - SMALL Formula'!$B$2:$B$12)-1),ROW('INDEX - SMALL Formula'!1:1)),1),"")`

And, itâ€™s working without any errors. So, there must be another problem with your workbook. So, please share it with us thus we can solve your issue.

If there is any other problem regarding Excel, you can let us know. Also, follow our website,

ExcelDemy, a one-stop Excel solution provider to explore more. Happy Excelling.it is not working for me i tried using

=IFERROR(INDEX($B$5:$B$10,SMALL(IF(F$4=$C$5:$C$10,ROW($B$5:$B$10)),ROW(1:1))-4,1),”NA”)

but it shows only first value and not beyond

Values in Col B

Name(Heading)

Ram

Shyam

Ravi

Luxman

raj

Hanuman

and col C

Type(heading)

God

Human

Human

God

Human

God

in cell F4 (God) and G4(Human)

output in F5 shows Ram and remaining NA and in G5 also NA

Pls suggest what is mistake

Hi Anil, here is an easier and shorter formula for you. (

FILTER functionis available from Excel 2019)`=FILTER($B$5:$B$10,$C$5:$C$10=F4)`

Do the following to your desired list.

>> Write “God” in cell

F4and “Human” in cellG4.>> Copy the above formula and paste it into cell

F5(it will list God Names)>> Similarly generate the Human Names. Just use

G4instead ofF4in the given formula.Can you visualize what to do? Please let us know if this helps.

Regards

-ExcelDemy Team

When I copy and paste your formula, it responds great for the first 8 rows. When I expand to include cells B5:B144 and C5:C144 it returns #N/A. Can you please help?

Thank You

Hello

RENEThanks for reaching out and sharing your problem. You have observed that the formula works for the first eight rows. When you expand to include cells B5:B144 and C5:C144, it returns

#N/A. However, our machine works perfectly fine, even though we work with an extensive range. TheFILTERfunction is only available in Excel forMicrosoft 365andExcel 2021. Assuming you are using one of these versions.To resolve your problem, double-check the references in the formula to ensure they correspond to the correct columns and cells. Confirm that the formula is entered correctly without any typos or syntax errors. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

I’m not sure if this is the best option for my need. I am being told I will be given a project next week where a list of materials will result from multiple selections that are setup in pick lists. I can setup the pick lists but will need some advice on how to get resulting list, based on what selections were made. I would love some advice.

Hi DARRELL,

Congratulations on your new project. Be confident, you can make it. Based on your selections, you may use any of the following to get resulting list:

1.

IF function: You can use the “IF” function to create a formula that checks if certain criteria are met and return the appropriate value. For example, if you have a pick list for materials and another for color, you can use an “IF” function to generate a list of materials that match the selected color.2.

VLOOKUP function: The “VLOOKUP” function can be used to search for a value in a table and return a corresponding value. You can set up a table with all the possible combinations of selections and use “VLOOKUP” to generate the resulting list based on the selections made.3.

FILTER function: The “FILTER” function can be used to filter a list based on certain criteria. You can set up a table with all the materials and their attributes (such as color, size, etc.) and use “FILTER” to generate a list of materials that match the selected attributes.4.

PivotTable: Pivot tables can be used to analyze and summarize data in a table. you can set up a table with all the selections made and your corresponding materials and use a pivot table to generate a list of materials based on the selections made.These are just a few options you can consider. You will need to choose the one that works best for your specific situation. Don’t hesitate to contact with us if you face any problem. Best of luck.

Regards

Rafiul HasanTeam ExcelDemy

Hello,

My questions is I want to create a checklist that if I choose one of the options it brings up the tasks for that option. For example, my choices would be green or blue. If I choose blue, it has it’s own tasks that differ than green. How would I go about doing that?

Hello RENAE HINES,

Hope you are doing well. Thank you for your query. You can create a dynamic checklist using Excel’s built-in feature such as

Data Validation.E5and go toÂData >> Data Tools >> Data Validationto get the particular color.Data ValidationÂdialog box will pop up. SelectListÂfrom the drop-down menu ofValidation criteriaandSourceasE14:E15.OKto complete the Data validationÂprocess.E6to complete the dynamic checklist of getting task colorwise.`=FILTER($C$5:$C$16,EXACT($B$5:$B$16,E5))`