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)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td]MONDAY[/td][td]TUESDAY[/td][td]WEDNESDAY[/td][td]THURSDAY[/td][td]FRIDAY[/td][td]SATURDAY[/td][td]SUNDAY[/td][/tr]

[tr][td]

2​

[/td][td]EMP. ID[/td][td]Name[/td][td][/td][td]

4/27/2015​

[/td][td]

4/28/2015​

[/td][td]

4/29/2015​

[/td][td]

4/30/2015​

[/td][td]

5/1/2015​

[/td][td]

5/2/2015​

[/td][td]

5/3/2015​

[/td][/tr]

[tr][td]

3​

[/td][td]

5678​

[/td][td]John[/td][td][/td][td][/td][td]DC1234[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

5678​

[/td][td]John[/td][td][/td][td][/td][td][/td][td][/td][td]DC1234[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

5678​

[/td][td]John[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]DC1235[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

5896​

[/td][td]Tim[/td][td][/td][td]DC1235[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

5896​

[/td][td]Tim[/td][td][/td][td][/td][td]DC1235[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

5627​

[/td][td]Lachlan[/td][td][/td][td][/td][td][/td][td]DC1234[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

5627​

[/td][td]Lachlan[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]DC1235[/td][/tr]
[/table]

output

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td]MONDAY[/td][td]TUESDAY[/td][td]WEDNESDAY[/td][td]THURSDAY[/td][td]FRIDAY[/td][td]SATURDAY[/td][td]SUNDAY[/td][/tr]

[tr][td]

2​

[/td][td]EMP. ID[/td][td]Name[/td][td][/td][td]

4/27/2015​

[/td][td]

4/28/2015​

[/td][td]

4/29/2015​

[/td][td]

4/30/2015​

[/td][td]

5/1/2015​

[/td][td]

5/2/2015​

[/td][td]

5/3/2015​

[/td][/tr]

[tr][td]

3​

[/td][td]

5678​

[/td][td]John[/td][td][/td][td][/td][td]DC1234[/td][td][/td][td]DC1234[/td][td]DC1235[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

5896​

[/td][td]Tim[/td][td][/td][td]DC1235[/td][td]DC1235[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

5627​

[/td][td]Lachlan[/td][td][/td][td][/td][td][/td][td]DC1234[/td][td][/td][td][/td][td][/td][td]DC1235[/td][/tr]
[/table]

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?