Formula to bring back a list of cell values

MarkAn

Board Regular
Joined
Sep 28, 2005
Messages
69
Office Version
  1. 2010
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top