How to populate rows based on specified cell value in Excel?
Supposing you have a project table with corresponding person’s name who is in charge of each project as below screenshot shown. And now you need to list out all project names in rows based on given person, how to achieve it? Actually an array formula in this article can help you solve the problem.
Easily select entire rows based on cell value in a certian column:
The Select Specific Cells utility of Kutools for Excel can help you quickly select entire rows based on cell value in a certian column in Excel as below screenshot shown. After selecting all rows based on cell value, you can manually move or copy them to a new location as you need in Excel.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now
Populate rows based on specified cell value with array formula
Please do as follows to populate rows with the corresponding record based on given value in Excel.
1. Select a blank cell, enter formula =IFERROR(INDEX(Sheet2!A$1:A$10,SMALL(IF(Sheet2!B$1:B$10=D$2,ROW(A$1:A$10)),ROWS(D$2:D2))),"") into Formula Bar, and then press the Ctrl + Shift + Enter keys. See screenshot:
Note: in the formula, Sheet2 is the name of current worksheet, A$1:A$10 is the column range contains all project names (include header), B$1:B$10 is the column range contains all person names (include header), and D$2 is the cell contains the person name you will populate rows based on. Please change them as you need.
2. Select the first result cell, drag the Fill Handle down to fill all rows with corresponding task names. See screenshot:
Recommended Productivity Tools for Excel
- More than
300 powerful advanced features, designed for
1500 work scenarios, increasing productivity by
70%, give you more time to take care of family and enjoy life.
- No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
- Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds,
- Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
- 110,000 highly effective people and 300+ world-renowned companies' choice.
- 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.
- Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by
50%, and reduces hundreds of mouse clicks for you every day!