encountering error in index/match formula

[lui de sagun]

Hi,

Need your help so badly please....

I used the formula:

=IFERROR(INDEX(Sheet1!$D$1:$K$1,1,SUMPRODUCT(($C3=Sheet1!$B$1:$B$100)*(Sheet2!$D3=Sheet1!$C$1:$C$100)*(Sheet2!E$1=Sheet1!$D$1:$K$100)*COLUMN($E:$L))-4),"")

to retrieve date values from row1 of sheet1 and put each date in under column1 of sheet2 based on the value (ex: for Customer Aboitiz Jebsen, this is Stage_3 under 12/2/2014 of sheet1. I need to put 12/2/2014 under Stage_3 column with the same customer and so on. Above formula worked for some (upto line 35) but no value when same formula was applied for the rest. Also, when I am adjusting Sheet1!$D$1:$K$1 to Sheet1!$D$1:$BI$1, error is seen.

I attached the file I am using for your reference.

 

[Somendra Misra]

Hi,

Try below array formula in E3 and copy down and across:

=IFERROR(INDEX(Sheet1!$D$1:$K$1,,SMALL(IF((Sheet2!$C55=Sheet1!$B$3:$B$78)*(Sheet2!$D55=Sheet1!$C$3:$C$78)*(Sheet2!G$1=Sheet1!$D$3:$K$78),COLUMN(Sheet1!$D$1:$K$1)-COLUMN(Sheet1!$D$1)+1),1)),"")

Regards,

 

[NARAYANK991]

Hi ,

See your file.

Narayan

 

[John()]

What i found was your never adjusted your cell ref after reaching row 35 so for second part you were still searching top area i just changed your cell ref to read
$C30=Sheet1!$B$1:$B$78) for the relevant sections on your formula as yours only read eg $B$1:$B:$35
so i persume you did first section and when it worked out you added the next section data but didnt adjust your formula to take into account the extra rows being searched
so in all i just changed
(($C3=Sheet1!$B$1:$B$78)*(Sheet2!$D3=Sheet1!$C$1:$C$78)*(Sheet2!E$1=Sheet1!$D$1:$K$78) rest dindnt need any change