The attached spreadsheet extract (please excuse the format, and why can't I upload the spreadsheet?!?)is a much smaller version of a live problem I can't fix on a much larger project planning spreadsheet.
In column B there is a list of activities, column C shows the week the activity is planned to occur, and column A shows whether the activity is a milestone activity or not. As per the example the week numbers cannot necessarily be arranged in numeric order.
I need a solution to allow me to automatically generate the content of column G. The logic being that where column A shows a milestone, the activity name should be dropped into column G where the week number in F matches that in C.

I have tried array formulas without success and am totally stuck for ideas.

Column A Column B Column C
Milestone Actvity Week
- Test 1 3
- test 2 2
- test 3 2
- test 4 3
Milestone test 5 1
- Test 6 3
Milestone Test 7 7
- Test 8 5
- Test 9 6
Milestone Test 10 4


Column F Column G
Week Milestone activity
1 test 5
2
3
4 Test 10
5
6
7 Test 7


Any advice/help would be greatly appreciated.

Thanks

Hi Steve,

Am Tue, 14 Apr 2015 21:09:20 +0100 schrieb SteveRH:

Column A Column B Column C
Milestone Actvity Week
- Test 1 3
- test 2 2
- test 3 2
- test 4 3
Milestone test 5 1
- Test 6 3
Milestone Test 7 7
- Test 8 5
- Test 9 6
Milestone Test 10 4


Column F Column G
Week Milestone activity
1 test 5
2
3
4 Test 10
5
6
7 Test 7

in G2 try:
=IFERROR(INDEX(B:B,MATCH("Milestone"&F2,$A$1:$A$10 0&$C$1:$C$100,0)),"")
and insert the array formula with CTRL+Shift+Enter and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus, you are a lifesaver, the formula works brilliantly, I just need to spend some time learning the Index and Match functions now.

This has thrown up one further issue though, being that if there are 2 or 3 milestones in the same week I am currently only able to show the first one. I know I can paste your formula into an array of cells to obtain multiple results but that seems only to work if the results array is a vertical one. I could really do with returning the multiple results horizontally in columns G, H and I. Is there a genius solution to this?

Hi Steve,

Am Wed, 15 Apr 2015 13:44:38 +0100 schrieb SteveRH:

This has thrown up one further issue though, being that if there are 2
or 3 milestones in the same week I am currently only able to show the
first one. I know I can paste your formula into an array of cells to
obtain multiple results but that seems only to work if the results array
is a vertical one. I could really do with returning the multiple
results horizontally in columns G, H and I. Is there a genius solution
to this?

list all occurances of your weeks in column F.
Then try in G2:
=IFERROR(INDEX($B$1:$B$100,SMALL(IF(($A$1:$A$100=" Milestone")*($C$1:$C$100=F2),ROW($1:$100)),COUNTIF ($F$2:F2,F2))),"")
insert the array formula with CTRL+Shift+Enter and copy down.

Or look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for workbook "INDEX"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional