Match Date range with other Criteria & matching from a Table

[Tysond]

hello I have fried my brain on this one. I am trying to return a result based on date, day & Duty number.
D10 is giving me the headache.
D10=INDEX($K$2:$C$6,MATCH(1,INDEX(($A$2:$A$6<=B10)*(!$B$2:$B$6>=B10)*($J$2:$J$6=D12),0,1),0)) & then I'm stuck!
(This becomes part of another formula in another cell that will need the day & rev number to pick the hours out)

any ideas? thank you in advance

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date Start[/TD]
[TD]Date End[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Duty Number[/TD]
[TD]Rev Number[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/01/2017[/TD]
[TD]15/12/2017[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]100[/TD]
[TD]1A11[/TD]
[TD]07:50[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16/12/2017[/TD]
[TD]22/02/2018[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]100[/TD]
[TD]2B22[/TD]
[TD]08:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]23/02/2018[/TD]
[TD]22/12/2222[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]100[/TD]
[TD]3B33[/TD]
[TD]07:50[/TD]
[TD]16:50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/01/2017[/TD]
[TD]22/12/2222[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]1A12[/TD]
[TD]07:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]03/01/2017[/TD]
[TD]22/12/2222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD]1A12[/TD]
[TD]09:00[/TD]
[TD]19:00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Duty Number[/TD]
[TD]Rev Number[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Thursday[/TD]
[TD]01/02/2018[/TD]
[TD]200[/TD]
[TD]????[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Questions:
Why are you using D12 in your formula?
Do you want the row with a X in column Thursday?
What exactly are you trying to do? Expected result?

M.

 

[Tysond]

sorry that should not be D12 it should be D10.
???? in D10 should return 1A12.
X under the Days of the week denote the days the Duties are valid I could easily replace the "X" with "The Day" to make matching simpler. (the duties, numbers & Revisions our how my company works and brings it's own world of stress. Anyone with a brain would use different duty numbers to remove confusion but I have no control of that)

 

[Marcelo Branco]

Maybe this...

=INDEX(K2:K6,MATCH(1,INDEX((A2:A6<=B10)*(B2:B6>=B10)*(J2:J6=C10)*(INDEX(C2:I6,0,MATCH(A10,C1:I1,0))="X"),0),0))

M.

 

[Tysond]

Marcelo, that makes perfect scene! I will use that tomorrow and let you know how it works. Thank you so much!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Tysond]

worked perfectly thank you.