- To post as a guest, your comment is unpublished.· 2 years ago=IFERROR(INDEX(Sheet2!A$1:J$1,SMALL(IF(Sheet2!A$2:J$2=A$5,COLUMN(A$1:J$1)),COLUMNS(A$5:A5))),"")
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.
Please do as follows to populate rows with the corresponding record based on given value in Excel.
1. Select a blank cell, enter the below formula into it and then press the Ctrl + Shift + Enter keys.
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:
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.
Download and try it now! ( 30-day free trail)
- How to move entire row to another sheet based on cell value in Excel?
- How to lock or unlock cells based on values in another cell in Excel?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 years agoWhat if I wanted to populate the rows horizontally instead of vertically what would I change from the formula you put above: =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))),"")