What formula to use to return multiple values?

[Fliknatsume]

I have data that shows me what day and what DC a driver works out of.
This issue is, the driver may have driven multiple times for the week and will show up in the data in multiple rows.

I would like to use a formula so I can put it all in one row.
All the INDEX, MATCH, SMALL formulas I have been using, only pick up the first Value


*if its easier, I can send the spread sheet via email so you can understand what I am trying to do

This is the formula that I have working at the moment, but still only picks up the first value.
I also need to be able to drag this horizontally.

=IFERROR(INDEX(Table!G3:G176, SMALL(IF(Actual!$A$3=Table!$A$3:$A$176, ROW(Table!$A$3:$A$176)-ROW(Table!A3)+1), COLUMN(Actual!$A$3))),"")


Please help

 

[Aladin Akyurek]

Assuming that this formula goes in E2 (adjust to suit)...

Control+shift+enter, copy across, and down:

=IFERROR(INDEX(Table!G$3:G$176, SMALL(IF(Actual!$A$3=Table!$A$3:$A$176, ROW(Table!$A$3:$A$176)-ROW(Table!A3)+1), ROWS(E$2:E2))),"")

 

[Fliknatsume]

It still only returns the first value

 

[MARK858]

Did you enter the formula using Ctrl-Shift + Enter and not just Enter?

 

[Fliknatsume]

Yes I completed the formula by pressing ctrl+Shift+enter

And it still only picks up the first value picked up

 

[MARK858]

Very strange because I definitely get multiple results with the formula.

I would suggest posting a copy of your workbook on Box.com and then posting a link to the file in the thread.

 

[Aladin Akyurek]

Yes I completed the formula by pressing ctrl+Shift+enter

And it still only picks up the first value picked up

Try again...

=IFERROR(INDEX(Table!G$3:G$176, SMALL(IF(Actual!$A$3=Table!$A$3:$A$176, ROW(Table!$A$3:$A$176)-ROW(Table!$A$3)+1), ROWS(E$2:E2))),"")

 

[Fliknatsume]

Thank you, I have done so.
https://app.box.com/s/kv0ekqio3ps8g0e04y0kirk0s03bdnyb

 

[Aladin Akyurek]

Thank you, I have done so.
https://app.box.com/s/kv0ekqio3ps8g0e04y0kirk0s03bdnyb

What you should have done all along instead of posting a non-working formula: (1) try to post a sample on the board (within, not outside), using one of the methods:

http://www.mrexcel.com/forum/about-board/508133-attachments.html

Note that what is outside will perish more often and faster that what is directly on the board.

data (inputs)

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​				MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
2​	EMP. ID	Name		4/27/2015​	4/28/2015​	4/29/2015​	4/30/2015​	5/1/2015​	5/2/2015​	5/3/2015​
3​	5678​	John			DC1234
4​	5678​	John					DC1234
5​	5678​	John						DC1235
6​	5896​	Tim		DC1235
7​	5896​	Tim			DC1235
8​	5627​	Lachlan				DC1234
9​	5627​	Lachlan								DC1235

output

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​				MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
2​	EMP. ID	Name		4/27/2015​	4/28/2015​	4/29/2015​	4/30/2015​	5/1/2015​	5/2/2015​	5/3/2015​
3​	5678​	John			DC1234		DC1234	DC1235
4​	5896​	Tim		DC1235	DC1235
5​	5627​	Lachlan				DC1234				DC1235

Goal: A formula that obtains the results shown in D3:J5.

Solution (a would be helper would try to provide)...

D3, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(data!D$3:D$9,SMALL(IF(data!$A$3:$A$9=$A3,IF(INDEX(data!$D$3:$J$9,0,MATCH(D$2,data!$D$2:$J$2,0))<>"",ROW(data!$A$3:$A$9)-ROW(data!$A$3)+1)),ROWS(D3:D3))),"")

 

[Fliknatsume]

Thank you so much that worked.
The only thing (and I am sorry please bare with me) I could not drag down as well, it would not work.
Do I need to change what cells I am locking?