Look Up with Multiple Matches

[DanCull]

I am trying to create a spreadsheet that will generate action plans for colleagues on development programmes. What I would like to achieve is the following:

- one sheet will have an empty action plan form, including several empty cells which will be populated with the required tasks

- another sheet will list all of the development activities required to undertake and complete the development programme, plus an empty column where a date can be entered next to each task once it has been set

- I want to be able to enter the date into a cell on the action plan form, and then any tasks from the second sheet with the same date next to them in the date set column, will be extracted and entered into the blank cells on the action plan

I could achieve the above if there was just going to be one match, with a simple look up. However, there are likely to be a varying number (minimum 2) of tasks set for each plan, and so I am unsure how to achieve this.

Is there someway of using a look up function which will return an array with all matches, and then I can put each array result into a separate cell on the action plan.

 

[DanCull]

I think one option to achieve the above is if on the action plan template, I have say five blank cells ready to receive action plan tasks from the list on the second sheet, and there then be a maximum of five tasks that can be set.

Then in each of those cells, I use the INDEX function with index references 1 to 5 for the five cells respectively.

So I'd have something like =INDEX(A1:B20,1), where A1:B20 is the list of action tasks, but I want to be able to filter this array so that it only includes rows where the date in column B matches the date entered on the action plan sheet. The value in column A is what I want to display.

 

[cdchapman]

If you can save an example of how your worksheets are laid out, with sample data, and what you expect to be shown on the action plan form to somewhere like dropbox and provide a link on this thread, then I might be able to come up with a formula you can use.

Chris

 

[FDibbins]

Maybe something like this (IF I understand you?)...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

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

1​

[/td][td]date[/td][td]blah 1[/td][td]blah 2[/td][td][/td][td]date[/td][td]blah 1[/td][td]blah 2[/td][/tr]

[tr][td]

2​

[/td][td]

1/1/2017​

[/td][td]

10​

[/td][td]

100​

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

1/2/2017​

[/td][td]

20​

[/td][td]

200​

[/td][/tr]

[tr][td]

3​

[/td][td]

1/2/2017​

[/td][td]

20​

[/td][td]

200​

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

50​

[/td][td]

500​

[/td][/tr]

[tr][td]

4​

[/td][td]

1/3/2017​

[/td][td]

30​

[/td][td]

300​

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

80​

[/td][td]

800​

[/td][/tr]

[tr][td]

5​

[/td][td]

1/1/2017​

[/td][td]

40​

[/td][td]

400​

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

[tr][td]

6​

[/td][td]

1/2/2017​

[/td][td]

50​

[/td][td]

500​

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

[tr][td]

7​

[/td][td]

1/3/2017​

[/td][td]

60​

[/td][td]

600​

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

[tr][td]

8​

[/td][td]

1/1/2017​

[/td][td]

70​

[/td][td]

700​

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

[tr][td]

9​

[/td][td]

1/2/2017​

[/td][td]

80​

[/td][td]

800​

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

[tr][td]

10​

[/td][td]

1/3/2017​

[/td][td]

90​

[/td][td]

900​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
F2=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$E$2,ROW($A$2:$A$10)),ROWS($A$1:A1))),"")
ARRAY entered using CTRL SHIFCT ENTER, not just ENTER
Then copy across and then down as needed

 

[DanCull]

Thanks Ford, that does appear to provide a solution to my objective. I will try and implement that and let you know how I get on.

 

[FDibbins]

Keep me posted

 

[DanCull]

I'm struggling to transpose your provided formula to my spreadsheet - I have entered the following, but it says there is an error with my formula:

=IFERROR(INDEX(‘FDP Progress Matrix'!C:C,SMALL(IF(‘FDP Progress Matrix'!$D$2:$D$100=$B$1,ROW(‘FDP Progress Matrix'!$D$2:$D$100)),ROWS(‘FDP Progress Matrix'!$C$1:C1))),"")

In my spreadsheet, the look up targets are on a separate sheet called FDP Progress Matrix, on which column C lists the text that I would want to bring across to the current sheet, and column D contains the date that needs to match. The top row of the sheet is header text only.

In the sheet where the above formula is entered, the reference cell to match the date to is B1.

 

[FDibbins]

It all looks good, cant see any obvious problems.

1. what exactly does it say/do when you enter the formula?
2. Did you enter using CTRL SHIFT ENTER, and not just ENTER?
3. are your dates just dates, and not also include time?
4. are your dates (both B1 and col D) real dates and not text looking like dates? test with =isnumber(cell-ref)