Match Date to Nearest Month + Match with text field

[Caleeco]

	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Year-Month[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Date[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Year-Month[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Defects[/TD]
[TD="align: center"]Desired output[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]201909[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]15/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]201910[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]17/10/2019[/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]201909[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]201907[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]29/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]201907[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]15/07/2019[/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]201906[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]201907[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]28/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]201907[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]02/07/2019[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]201907[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]201907[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]01/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]201906[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]27/06/2019[/TD]
[TD="align: center"]14[/TD]
[TD="align: right"]201806[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]201906[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]05/06/2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]201906[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]18/06/2019[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]201907[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]201906[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]03/06/2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]201905[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]01/05/2019[/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]201905[/TD]

</tbody>

Sheet1​

Hello,

Im struggling with a data-matching problem (which I can solve using VBA) but would prefer a formula solution.

Data Setup

• Table 1 (Column B:D) is static data. Shows different products made each month (Year-Month)
• Table 2 (Column E:J) is static data aside from Column J which needs to evaluate Table 1

The Problem
For each row I need Column J to look at Table 1 and ask the following questions:

• Have I made Part X (Column B) in the same Month as the defects occured (Column H)? > If YES, return Matched YEARAP (From Table 1 or 2)
• If I have not made Part X (Column B) in the same month as the defects occurred, find the nearest matching month that product was made > return that matched Year-AP (from Table 1)
• If Part X is not found in Table 1, return Year-AP from Column F

Worked Example
Row 2 - Part A. Was not made in October (Table 1) > It is found in Table 1 > Nearest Matching month is September > Return 201909.


Is this possible? Any help is appreciated as always
Many Thanks

Caleeco

 

[jorismoerings]

Hi,

I can't match all your desired results for the obvious reason being I don't see 201806 in any of your table's.
Take a look at this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Year-Month	Product	Date		Year-Month	Product	Date	Defects	Desired output		Formula Output
2	2	201909	A	15-9-2019		201910	A	17-10-2019	13	201909		201909
3	3	201907	B	29-7-2019		201907	D	15-7-2019	15	201906		201906
4	4	201907	C	28-7-2019		201907	C	2-7-2019	4	201907		201907
5	5	201907	A	1-7-2019		201906	A	27-6-2019	14	201806		201907
6	6	201906	D	5-6-2019		201906	B	18-6-2019	8	201907		201907
7	7	201906	A	3-6-2019		201905	E	1-5-2019	13	201905		201905
Sheet2

Cell Formulas
Range	Formula
L2	{=IFERROR(INDEX($B$2:$B$7,MATCH(INDEX(IF($C$2:$C$7=$G2,$D$2:$D$7,999999),MATCH(MIN(ABS(IF($C$2:$C$7=$G2,$D$2:$D$7,999999)-$H2)),ABS(IF($C$2:$C$7=$G2,$D$2:$D$7,999999)-$H2),0)),$D$2:$D$7,0)),F2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Take a look at this:

I actually posted something along the same lines but then realised that it wasn't robust so removed it. I think the same occurs with yours - see J5 with my changed sample data below. By my understanding that should be 201906.

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	Year-Month	Product	Date		Year-Month	Product	Date	Defects	Output
2	202009	Z	15/09/2019		201910	X	17/10/2019	13	201910
3	201907	A	15/09/2019		201910	A	17/10/2019	13	202009
4	201907	C	28/07/2019		201907	C	2/07/2019	4	201907
5	201909	A	1/07/2019		201906	A	27/06/2019	14	201909
6	201906	D	5/06/2019		201906	B	18/06/2019	8	201906
7	201906	A	3/06/2019		201905	E	1/05/2019	13	201905
Match Near (2)

 

[jorismoerings]

Hi @Peter_SSs

Thoughts are similar but i didn't change the request of the OP being "nearest date". If OP added "past nearest date" or had intention to ask but forgot to mention it, my solution isn't right.
Let's wait and see what OP comes back with.

Any way past nearest date can also be solved so ....

 

[jorismoerings]

Added to post #4 .

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	Year-Month	Product	Date		Year-Month	Product	Date	Defects	Desired output		Nearest Date	Nearest Past Date
2	2	201909	A	15-9-2019		201910	A	17-10-2019	13	201909		201909	201909
3	3	201907	B	29-7-2019		201907	D	15-7-2019	15	201906		201906	201906
4	4	201907	C	28-7-2019		201907	C	2-7-2019	4	201907		201907	201907
5	5	201907	A	1-7-2019		201906	A	27-6-2019	14	201806		201907	201906
6	6	201906	D	5-6-2019		201906	B	18-6-2019	8	201907		201907	201906
7	7	201906	A	3-6-2019		201905	E	1-5-2019	13	201905		201905	201905
Sheet2

Cell Formulas
Range	Formula
L2	{=IFERROR(INDEX($B$2:$B$7,MATCH(INDEX(IF($C$2:$C$7=$G2,$D$2:$D$7,999999),MATCH(MIN(ABS(IF($C$2:$C$7=$G2,$D$2:$D$7,999999)-$H2)),ABS(IF($C$2:$C$7=$G2,$D$2:$D$7,999999)-$H2),0)),$D$2:$D$7,0)),F2)}
M2	{=IFERROR(INDEX($B$2:$B$7,MATCH(MAX((IF($C$2:$C$7=$G2,$D$2:$D$7,999999))*IF($C$2:$C$7=$G2,$D$2:$D$7,999999)),$D$2:$D$7,0)),F2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

First, I had some errors in my sample data in that previous post so please ignore my comments there. I'll try again here.

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	Year-Month	Product	Date		Year-Month	Product	Date	Defects	Output
2	202009	Z	15/09/2019		201910	X	17/10/2019	13	201910
3	201907	A	15/07/2019		201910	A	17/10/2019	13	201909
4	201907	C	28/07/2019		201907	C	2/07/2019	4	201907
5	201909	A	1/09/2019		201906	A	27/06/2019	14	201907
6	201906	D	5/06/2019		201906	B	18/06/2019	8	201906
7	201906	A	3/06/2019		201905	E	1/05/2019	13	201905
Match Near (2)

.. i didn't change the request of the OP being "nearest date".

Actually, the OP didn't request "nearest date" but ..

Have I made Part X (Column B) in the same Month as the defects occured (Column H)? > If YES, return Matched YEARAP (From Table 1 or 2)

So for that row 5 example, there was product A made in June 2019 so that should be the result.

And if there wasn't a product made in the same month, the request was ..

If I have not made Part X (Column B) in the same month as the defects occurred, find the nearest matching month that product was made > return that matched Year-AP (from Table 1)

.. not nearest date.

I'm not trying to be critical here, especially since I haven't been able to come up with a suitable formula, but trying to discuss to get more clarity.

 

[Caleeco]

@Peter_SSs @jorismoerings

Sorry for the delayed response, I've been pretty ill this week!

Thanks you so much for both your solutions! They both work great, although Peter is closest to my original brief (closest month, not date).

Kind Regards
Caleeco

 

[Peter_SSs]

Sorry for the delayed response, I've been pretty ill this week!

Thanks you so much for both your solutions! They both work great

You're welcome. Hope you are now fully recovered.