Required Desire Output using Index & Small Function

ravi2628

New Member
Joined
Dec 20, 2017
Messages
25
Hi Every One,

i required the Output which is highlighted in Green

Project NameWeek1Week2Week3Week4Person Name
P1
P22 5 S
P3 6 R
P4
P5 8R
P6
P7 5 R
P85 10R
P9
P10
P11
P12
Output
Person NameR
WeekWeek4
Existing Output
Project Name
1P30
2P58
3P70
4P810
5#NUM!#NUM!
6#NUM!#NUM!
Required Output
1P58
2P810
3
4
5
6

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You can adapt the following Array Formula

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

ravi2628

New Member
Joined
Dec 20, 2017
Messages
25
Hello,

You can adapt the following Array Formula

Code:
=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 NameWeek1Week2Week3Week4Person Name
P1
P22 5 S
P3 6 R
P4
P5 8R
P6
P7 5 R
P85 10R
P9 10R
P10
P11
P12
Output
Person NameR
WeekWeek4
Existing Output
Project Name
1P30
2P58
3P70
4P810
5P910
6#NUM!#NUM!
Required Output
Project has to get Automatically Populated based on ValueValue
1P58
2P810
3P910
4
5
6

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

If you input area is located in cells A1 to F13

You can have the Array Formula in cell J2 :

Code:
=SMALL(OFFSET(A2:A13,0,MATCH("Week4",$A$1:$F$1,0)-1),ROWS(J$2:J2))
and a standard formula in cell I2

Code:
=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
 

Forum statistics

Threads
1,085,543
Messages
5,384,338
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top