Formula to bring back a list of cell values

MarkAn

Board Regular
Joined
Sep 28, 2005
Messages
58
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.
 

Some videos you may like

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,974
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,959
Members
410,586
Latest member
acadavid86
Top