excel sheet that contains different stages on a weekly basis

[lui de sagun]

Hi,

I need your expertise in solving my excel problem.

I have an excel sheet that contains different stages on a weekly basis. I want to have it differently - using the same file, the column names will now be Stages 1 to 5 and the values on rows are the dates. I have attached my working file (Sheet1). In Sheet2, I highlighted my sample result in yellow.

Appreciate your help please.

 

[Smallman]

Hi lui

The following in D2 of sheet2 drag across and down.

=IFERROR(INDEX(Sheet1!$D$2:$D$19,MATCH(1,INDEX((Sheet1!$B$2:$B$601=$B2)*(Sheet1!$C$2:$C$601=$C2)*(Sheet1!$D$2:$D$601=D$1),0),0)),"")

It is not an array formula so all good.

File attached to show workings.

Take care

Smallman

 

[prasaddn]

Hi,

The formula of Smallman's gets the stage, but i believe the requirement is to fetch the date of the stage.

I have used CSE formula very similar one:
=IFERROR(INDEX(Sheet1!$D$1:$H$1,1,SUMPRODUCT(($B2=Sheet1!$B$1:$B$100)*(Sheet2!$C2=Sheet1!$C$1:$C$100)*(Sheet2!D$1=Sheet1!$D$1:$H$100)*COLUMN($D:$H))-3),"")

Attached is the file with results.

Regards,
Prasad DN.

 

[Smallman]

Good point Prasad.

Because the required result uses dates which are numbers in disguise we can just use a straight sumproduct formula and format our cells to suit.

=SUMPRODUCT((Sheet1!$B$2:$B$601=$B2)*(Sheet1!$C$2:$C$601=$C2)*(Sheet1!$D$2:$H$601=D$1)*(Sheet1!$D$1:$H$1))

Makes things a little simpler. File attached to show workings.

Take care

Smallman

 

[lui de sagun]

Great! Thanks a lot Prasad DN. Really appreciate it!