If 2 columns match, and a date is on/between date range, return this value

[bran987]

This is so far above my head I am wondering if you guys can help me! Columns A-D are what I want to refer to. DOG/MOM/DAD all have the same date ranges by week. Each combination of DOG/MOM/DAD and the dates of the week on or between column B & C receive a unique code which is in Column D.

What I want to do is then have a formula look at Columns E & F, find a match for Column E in Column A, then look at the date in Column F to know which number in Column D to return.

So the formula I want to be in Column G does this: IF the text in E matches something in A, AND F is on or between the dates in B & C, return the value in Column D.

Thank you so much!!

REFERENCE TABLE                                                                RESULTS

             A                B                   C                 D                  E                    F                      G                

       DOG            01/01/18          01/07/18          01               DOG            01/20/18          03
       DOG            01/08/18          01/14/18          02               DAD            01/15/18          11
       DOG            01/15/18          01/21/18          03               MOM            01/14/18          06
       DOG            01/22/18          01/28/18          04               DAD            01/01/18          09
       MOM            01/01/18          01/07/18          05               DOG            01/04/18          01
       MOM            01/08/18          01/14/18          06               MOM            01/28/18          08
       MOM            01/15/18          01/21/18          07
       MOM            01/22/18          01/28/18          08
       DAD            01/01/18          01/07/18          09
       DAD            01/08/18          01/14/18          10
       DAD            01/15/18          01/21/18          11
       DAD            01/22/18          01/28/18          12

 

[markmzz]

Hi!

Try this in G1 and copy down:

=INDEX(D$1:D$12,MATCH(1,INDEX((E1=A$1:A$12)*(F1>=B$1:B$12)*(F1<=C$1:C$12),),0))

Or this in G1 and copy down

=TEXT(SUMPRODUCT(--$D$1:$D$12,--($A$1:$A$12=E1),--($B$1:$B$12<=F1),--($C$1:$C$12>=F1)),"00")

Markmzz

 

[bran987]

Mark thanks so much!!!! It works perfectly. Have a great weekend!

 

[markmzz]

Mark thanks so much!!!! It works perfectly. Have a great weekend!

You're welcome and thanks for the feedback.

Markmzz