# Formula to bring back a list of cell values

#### MarkAn

##### Board Regular
Hi
I have a worksheet (Sheet 1) with a lot of data
Row 1 holds dates for a 6 month period
Column A holds names of 500 people and then from Column B onwards it holds inputted times, I.e. 8am, 9am, 10am...….5pm, 8pm, these differ on a daily basis for each individual person

What I was looking for, was on another worksheet (Sheet 2), I have the same dates in Row 2
I would like then a formula that will look up the date in row 2 (I.e. A2 = 01/12/20, B2 = 02/12/20, C2 = 03/12/20 etc.), it would then look up the specific time entered in cell A1 and it will then list all those that have entered that criteria below each date (there would be a maximum of 10 listed people per date).

So it would return information like:

8pm
01/12/20 02/12/20 03/12/20
Person 2 Person 75 Person 8
Person 5 Person 95 Person 28
Person 15 Person 368 Person 56
Person 89 Person 369 Person 100
Person 150 Person 411 Person 248
Person 250 Person 400
Person 350

Any help would be greatly appreciated.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### jasonb75

##### Well-known Member
In your post, you say this
on another worksheet (Sheet 2), I have the same dates in Row 2
then in the next line, you say
a formula that will look up the date
which implies that the dates might not be the same.

If the dates are exactly the same, i.e. the same dates in the same order, no extra or missing dates in either sheet then you could try this in A2, fill right and down as needed.

=IFERROR(INDEX(Sheet1!\$A\$2:\$A\$501,AGGREGATE(15,6,ROW(Sheet1!B\$2:B\$501)/(Sheet1!B\$2:B\$501=\$A\$1),ROWS(A\$2:A2))),"")

If there are variations in the dates listed then this will give you incorrect results. A longer formula will be needed to allow for that.

#### MarkAn

##### Board Regular
This works great, many many thanks for your help

Replies
5
Views
151
Replies
6
Views
46
Replies
2
Views
102
Replies
2
Views
49
Replies
1
Views
45