Required Desire Output using Index & Small Function

R

ravi2628

Board Regular
Joined
Dec 20, 2017
Messages
221
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Every One,

i required the Output which is highlighted in Green

[TABLE="width: 573"]
<colgroup><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Project Name[/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD]Week4[/TD]
[TD]Person Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P2[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD]S[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P3[/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]R[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]R[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P7[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]R[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P8[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD]R[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Person Name[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Week[/TD]
[TD]Week4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Existing Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Project Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]P5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]P7[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]P8[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Required Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]P8[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
 
Upvote 0
James006 said:
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
Click to expand...


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




[TABLE="width: 753"]
<colgroup><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Project Name[/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD]Week4[/TD]
[TD]Person Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P2[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P3[/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P7[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P8[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Person Name[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Week[/TD]
[TD]Week4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Existing Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Project Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]P5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]P7[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]P8[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]P9[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Required Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Project has to get Automatically Populated based on Value[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]P8[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]P9[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Question Question
Countif to show result show only once per match
Replies
3
Views
229
Cubist
Cubist
Emerlin
  • Question Question
Combine rank values and auto-sort with formula and limited/no helper columns
Replies
8
Views
286
Peter_SSs
Peter_SSs
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
315
cokeoverpepsi123
C
V
  • Solved
Average Function
Replies
8
Views
395
Peter_SSs
Peter_SSs
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

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