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.

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

<tbody>
</tbody>

I used Name range for the sheet name Cal

Date	=Cal!$C$8:$C$23
Design	=Cal!$E$8:$E$23
Loom_no	=Cal!$D$8:$D$23
count	=Cal!$F$8:$F$23

<tbody>
</tbody>

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.