Look Up with Multiple Matches

DanCull

New Member
Joined
Oct 13, 2017
Messages
7
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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
 
Upvote 0
Maybe something like this (IF I understand you?)...
A​
B​
C​
D​
E​
F​
G​
1​
dateblah 1blah 2dateblah 1blah 2
2​
1/1/2017​
10​
100​
1/2/2017​
20​
200​
3​
1/2/2017​
20​
200​
50​
500​
4​
1/3/2017​
30​
300​
80​
800​
5​
1/1/2017​
40​
400​
6​
1/2/2017​
50​
500​
7​
1/3/2017​
60​
600​
8​
1/1/2017​
70​
700​
9​
1/2/2017​
80​
800​
10​
1/3/2017​
90​
900​
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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