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].

[tab1]	A	B	C
1	Customer#	dateSent	colorPref
2	12345	1/5/2018	red
3	ABCDE	2/2/2018	green
4	12346	2/2/2018	blue
5	CCCDD	3/5/2018	red

<tbody>
</tbody>

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.

[tab2]	A	B	C
1	Customer#	colorPref	dateSelected
2	12345	red	1/5/2016
3	12345	blue	1/6/2016
4	12345	red	12/1/2017
5	ABCDE	red	3/2/2017
6	ABCDE	green	2/1/2018
7	ABCDE	blue	2/15/2018
8	12346	null	null
9	CCCDD	green	3/1/2015

<tbody>
</tbody>

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:

[tab1]	A	B	C	D	E
1	Customer#	dateSent	colorPref	TEST	colorPref at dateSent
2	12345	1/5/2018	red	Y	red
3	ABCDE	2/2/2018	green	Y	green
4	12346	2/2/2018	blue	N	ref
5	CCCDD	3/5/2018	red	N	green

<tbody>
</tbody>

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")}