Matching between Multiple Dates

[kparadise]

Hello,

I have two tabs. [tab1] is a mailing list. This tab holds a bunch of information regarding a marking letter which was sent; information fields are: [Customer#], [dateSent], and [colorPrefer].

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][tab1]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Customer#
[/TD]
[TD="align: center"]dateSent
[/TD]
[TD="align: center"]colorPref
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]12345
[/TD]
[TD]1/5/2018
[/TD]
[TD]red
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]ABCDE
[/TD]
[TD]2/2/2018
[/TD]
[TD]green
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]12346
[/TD]
[TD]2/2/2018
[/TD]
[TD]blue
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]CCCDD
[/TD]
[TD]3/5/2018
[/TD]
[TD]red
[/TD]
[/TR]
</tbody>[/TABLE]

The other tab [tab2]; holds the color preference selected by each customer along with the date they selected this preference: [Customer#], [colorPref], and [dateSelected]. A customer can only make one color selection at a time, however they can choose a different color at any point in time.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][tab2]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Customer#
[/TD]
[TD]colorPref
[/TD]
[TD]dateSelected
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]12345
[/TD]
[TD]red
[/TD]
[TD]1/5/2016
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]12345
[/TD]
[TD]blue
[/TD]
[TD]1/6/2016
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]12345
[/TD]
[TD]red
[/TD]
[TD]12/1/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]ABCDE
[/TD]
[TD]red
[/TD]
[TD]3/2/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]ABCDE
[/TD]
[TD]green
[/TD]
[TD]2/1/2018
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]ABCDE
[/TD]
[TD]blue
[/TD]
[TD]2/15/2018
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]12346
[/TD]
[TD]null
[/TD]
[TD]null
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]CCCDD
[/TD]
[TD]green
[/TD]
[TD]3/1/2015
[/TD]
[/TR]
</tbody>[/TABLE]

If the Customer# has never selected a color preference (e.g. 12346), for the purpose of this exercise; the color pref will default to red.

What I am trying to ask the workbook on [tab1] is...When this marketing letter was sent, did the correct color flyer go out, based on the customers color preference [TEST]. Then also I would love to add a column to ask whether the test passed or failed; what was the color supposed to be at the date the marketing flyer was sent [colorPref at dateSent].

ANSWERS:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][tab1]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Customer#
[/TD]
[TD]dateSent
[/TD]
[TD]colorPref
[/TD]
[TD]TEST
[/TD]
[TD]colorPref at dateSent[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]12345
[/TD]
[TD]1/5/2018
[/TD]
[TD]red
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]red
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]ABCDE
[/TD]
[TD]2/2/2018
[/TD]
[TD]green
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]green
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]12346
[/TD]
[TD]2/2/2018
[/TD]
[TD]blue
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]ref
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]CCCDD
[/TD]
[TD]3/5/2018
[/TD]
[TD]red
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]green
[/TD]
[/TR]
</tbody>[/TABLE]


Again, if the Customer# was not found in [tab2] it is going to default to red.

Formula D2:
Formula E2:

 

[kparadise]

***Correction; E4 of the last table is supposed to say "red"

 

[kparadise]

I believe I figured it out...

I can create a simple formula for D2 once I completed this E2 formula...does anyone see an issue with this?

Formula E2: {=IFERROR(INDEX(tab2!$B:$B,MATCH(A2&MAX(IF(tab2!$A:$A=A2,IF(tab2!$C:$C<=B2,tab2!$C:$C),)),tab2!$A:$A&tab2!$C:$C,0)),"red")}