Required Desire Output using Index & Small Function

[ravi2628]

Hi Every One,

i required the Output which is highlighted in Green

	Project Name	Week1	Week2	Week3	Week4	Person Name
	P1
	P2	2		5		S
	P3		6			R
	P4
	P5				8	R
	P6
	P7		5			R
	P8	5			10	R
	P9
	P10
	P11
	P12
	Output
	Person Name	R
	Week	Week4
	Existing Output
	Project Name
1	P3	0
2	P5	8
3	P7	0
4	P8	10
5	#NUM!	#NUM!
6	#NUM!	#NUM!
	Required Output
1	P5	8
2	P8	10
3
4
5
6

<colgroup><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[James006]

Hello,

You can adapt the following Array Formula

=SMALL(OFFSET($A$2:$A$13,0,MATCH("Week4",$A$1:$F$1,0)-1),ROWS(J$2:J2))

Hope this will help

 

[ravi2628]

Hello,

You can adapt the following Array Formula

=SMALL(OFFSET($A$2:$A$13,0,MATCH("Week4",$A$1:$F$1,0)-1),ROWS(J$2:J2))

Hope this will help

Hi your formula worked but i have issue with it i want project name also to be populated automatically , and i have some query while working so i gave the example again.


Thanks & Regards,
Ravi

	Project Name	Week1	Week2	Week3	Week4	Person Name
	P1
	P2	2		5		S
	P3		6			R
	P4
	P5				8	R
	P6
	P7		5			R
	P8	5			10	R
	P9				10	R
	P10
	P11
	P12
	Output
	Person Name	R
	Week	Week4
	Existing Output
	Project Name
1	P3	0
2	P5	8
3	P7	0
4	P8	10
5	P9	10
6	#NUM!	#NUM!
	Required Output
	Project has to get Automatically Populated based on Value	Value
1	P5	8
2	P8	10
3	P9	10
4
5
6

<colgroup><col><col><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[James006]

Hello,

If you input area is located in cells A1 to F13

You can have the Array Formula in cell J2 :

=SMALL(OFFSET(A2:A13,0,MATCH("Week4",$A$1:$F$1,0)-1),ROWS(J$2:J2))

and a standard formula in cell I2

=INDEX($A$1:$A$13,MATCH(J2,INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Week4",$1:$1,0),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,MATCH("Week4",$1:$1,0),4),1,"")),0))

Hope this will help