find value according to multiple criteria

[akshay6s]

hi,
i have a production planing sheet. in sheet 1 column A is of loom no. column.

in sheet 2 there are details of all the looms which are in running or out of order. in sheet 2 column A:M is of day shift column and column N:AC is of night shift column.

in sheet 2 column k is the column where we fills the detail of every loom in day shift. so whenever warp cuts we fill that in the k column for day shift.

and same for night shift. only column will be changed which is y column.

so my requirement is that whenever we fill the date in C2 & F2 in sheet 1.
there should be date come in sheet 1 in column D & E respectively.

for example:- currently dates in sheet1! C2 & F2 are 7/13/2020/ to 7/20/2020.
and if we look in to sheet1!A:M in column k then we find that loom no.9 &13 got their warp cut on 7/18/2020.

so 7/18/2020 should be automatically come in D11 & D13

and loom no.6 got it's warp cut on 7/20/2020. so this date should be come in D8.

while loom no. 16 got it's warp cut during night shift on 7/20/2020 (cell- Y529)

so the date 7/20/2020 should be automatically shown in E16 in sheet1.

and whenever date gets changed the result should be according to that.

i have made a desired result sheet and have filled dates accordingly.


please give appropriate formula which can fill the requirements. i am hoping to get this as solved.

[Bo_Ry]

Please try at D5
=IFERROR(LOOKUP(1,1/(Sheet2!$A$2:$A$2209>=$C$2)/(Sheet2!$A$2:$A$2209<=$F$2)/(Sheet2!$K$2:$K$2209=D$3)/(Sheet2!$B$2:$B$2209=$A5),Sheet2!$A$2:$A$2209),"")

or if only 1 date match criteria

=SUMIFS(Sheet2!$A$2:$A$2209,Sheet2!$A$2:$A$2209,">="&$C$2,Sheet2!$A$2:$A$2209,"<="&$F$2,Sheet2!$B$2:$B$2209,$A5,Sheet2!$K$2:$K$2209,D$3)

[akshay6s]

very very perfectly done. and thanks a lot for giving two solutions. this is what a real expert should be. again thanks a lot.