# Required Desire Output using Index & Small Function

#### ravi2628

##### New Member
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

##### Well-known Member
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
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 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

##### Well-known Member
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