Value to be returned based on 3 criteria and unsorted dates.

[excelindianfanclub]

Hi Excel gurus and Excel Lovers,

Missed a lot for months.Here my troublesome question.

[TABLE="width: 537"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Loom no[/TD]
[TD]Design[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]08 Oct 2013[/TD]
[TD]3[/TD]
[TD]CB[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]07 Oct 2013[/TD]
[TD]3[/TD]
[TD]CB[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]06 Oct 2013[/TD]
[TD]9[/TD]
[TD]BA[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]05 Oct 2013[/TD]
[TD]13[/TD]
[TD]BC[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]04 Oct 2013[/TD]
[TD]14[/TD]
[TD]CA[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]02 Oct 2013[/TD]
[TD]6[/TD]
[TD]AB[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]30 Sep 2013[/TD]
[TD]15[/TD]
[TD]CB[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]27 Sep 2013[/TD]
[TD]11[/TD]
[TD]AC[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]27 Sep 2013[/TD]
[TD]11[/TD]
[TD]AC[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]25 Sep 2013[/TD]
[TD]11[/TD]
[TD]BC[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]24 Sep 2013[/TD]
[TD]13[/TD]
[TD]BB[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]23 Sep 2013[/TD]
[TD]2[/TD]
[TD]AB[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]22 Sep 2013[/TD]
[TD]11[/TD]
[TD]AC[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD]23 Sep 2013[/TD]
[TD]11[/TD]
[TD]AC[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD]23 Sep 2013[/TD]
[TD]15[/TD]
[TD]BC[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD]20 Sep 2013[/TD]
[TD]3[/TD]
[TD]CC[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Loom no[/TD]
[TD]Design[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]29-Sep-13[/TD]
[TD]11[/TD]
[TD]AC[/TD]
[TD]85[/TD]
[/TR]
</tbody>[/TABLE]


I used Name range for the sheet name Cal
[TABLE="width: 218"]
<tbody>[TR]
[TD]Date[/TD]
[TD]=Cal!$C$8:$C$23[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]=Cal!$E$8:$E$23[/TD]
[/TR]
[TR]
[TD]Loom_no[/TD]
[TD]=Cal!$D$8:$D$23[/TD]
[/TR]
[TR]
[TD]count[/TD]
[TD]=Cal!$F$8:$F$23[/TD]
[/TR]
</tbody>[/TABLE]

Formula used in cell F26 is {=IF(OR(C26="",D26="",E26=""),"",INDEX(Count,LARGE(IF(((Loom_no=$D26)*(Design=$E26)*(Date<=$C26)),ROW(Count)-ROW(F8)+1),1),1))}#array formula.As per my formulation,im getting the value of count from cell F21(colored in red) but i need to get it from cell F16(colored in green).I figured out why i got the wrong output too.

Please help me to pick the value of count, even dates are not sorted properly and also if i sort the date from largest to smallest.A issue is that the count will be revised(changed) for same design on the same date.

My goal is to get the latest value of count for the earliest or equal to the date given in cell C26 and also for given design and loom no

Hope i get the better solution.

 

[excelindianfanclub]

Formula used in cell F26 is{=IF(OR(C26="",D26="",E26=""),"",INDEX(Count,LARGE(IF(((Loom_no=$D26)*(Design=$E26)*(Date<=$C26)),ROW(Count)-ROW(F8)+1),1),1))}#array formula.As per my formulation,im getting the value of count from cell F21(colored in red) but i need to get it from cell F16(colored in green).I figured out why i got the wrong output too.

Please help me to pick the value of count, even dates are not sorted properly and also if i sort the date from largest to smallest.A issue is that the count will be revised(changed) for same design on the same date.

My goal is to get the latest value of count for the earliest or equal to the date given in cell C26 and also for given design and loom no

Hope i get the better solution.