Lookup Match Function with Variable Dates

[ShaunF]

Hi,

Hoping someone can assist with a formula to assist with a worksheet.

On SHEET 1, I have two relevant columns 'ID' and 'DATE'. For example -

[TABLE="width: 155"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]

Date​

[/TD]
[/TR]
[TR]
[TD="align: right"]465260[/TD]
[TD="align: right"]

16/08/18​

[/TD]
[/TR]
[TR]
[TD="align: right"]465260[/TD]
[TD="align: right"]03/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]465260[/TD]
[TD="align: right"]04/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]2859809[/TD]
[TD="align: right"]20/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]2859809[/TD]
[TD="align: right"]21/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]2859809[/TD]
[TD="align: right"]26/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]3066409[/TD]
[TD="align: right"]23/08/18[/TD]
[/TR]
[TR]
[TD="align: right"]7105709[/TD]
[TD="align: right"]21/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]7105709[/TD]
[TD="align: right"]24/09/18[/TD]
[/TR]
[TR]
[TD="align: right"]13111509[/TD]
[TD="align: right"]27/08/18[/TD]
[/TR]
</tbody>[/TABLE]


On SHEET 2, I have three relevant columns 'ID' ; 'DATE' ; 'RESULT'. For example -

[TABLE="width: 281"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]ID[/TD]
[TD]

DATE​

​

[/TD]
[TD]

RESULT​

​

[/TD]
[/TR]
[TR]
[TD="align: right"]

465260​

[/TD]
[TD="align: right"]

06/09/2018​

[/TD]
[TD]

ATT​

[/TD]
[/TR]
[TR]
[TD="align: right"]

146040008​

[/TD]
[TD="align: right"]

28/08/2018​

[/TD]
[TD]

DNAI​

[/TD]
[/TR]
[TR]
[TD="align: right"]

183694709​

[/TD]
[TD="align: right"]

30/09/2018​

[/TD]
[TD]

DNAD​

[/TD]
[/TR]
[TR]
[TD="align: right"]

259730008​

[/TD]
[TD="align: right"]

23/08/2018​

[/TD]
[TD]

NRE​

[/TD]
[/TR]
[TR]
[TD="align: right"]

​

[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What I am after is a formula, which tells me:

Use the ID and DATE from Sheet 1. If it finds a result in Sheet 2, where the ID is the same and the DATE is within the next 14 DAYS then return the RESULT. If no match found, then NIL.

In the above example, the second and third rows of Sheet 1 would return a result of ATT. The rest would be NIL.

Thanks,

Shaun.

 

[Peter_SSs]

If that Sheet2 data is in A1:C5, then try this, copied down.

Excel Workbook
	A	B	C
1	ID	Date
2	465260	16/08/2018
3	465260	3/09/2018	ATT
4	465260	4/09/2018	ATT
5	2859809	20/09/2018
6	2859809	21/09/2018
7	2859809	26/09/2018
8	3066409	23/08/2018
9	7105709	21/09/2018
10	7105709	24/09/2018
11	13111509	27/08/2018
Sheet1

 

[ShaunF]

Hi Peter,

Thanks for your help. Much appreciated.

When I use your formula it returns an error - You've entered too many arguments for this function.

Any ideas?

Thanks.

 

[Peter_SSs]

Hi Peter,

Thanks for your help. Much appreciated.

When I use your formula it returns an error - You've entered too many arguments for this function.

Any ideas?

Thanks.

Did you copy/paste my formula from the forum, or re-type it yourself?
Have you tried to modify it for your use? In doing that perhaps you have accidentally added/removed a "," or something?

If you click OK to that message does it highlight one particular part of the formula?

Suggest setting up a fresh workbook with only this sample data and layout and copy/paste the formula from the forum to show 'proof of concept'.

I have pasted my actual sheet so you can see that it is working here.

 

[Aladin Akyurek]

In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet2!$C$2:$C$5,MIN(IF(Sheet2!$A$2:$A$5=$A2,IF(Sheet2!$B$2:$B$5<=$B2+14,ROW(Sheet2!$A$2:$C$5)-ROW(INDEX(Sheet2!$A$2:$C$5,1,1))+1,9.99E+307),9.99E+307))),"")

 

[ShaunF]

Hi Peter,

Strange indeed. I have triple-checked everything. When pressing OK it highlights the '1' right towards the end of your formula.

Thanks.

 

[ShaunF]

Hi Aladin,

Thanks for your help. Formula works well - however, it is bringing through results where the date is less than the date in Sheet 1. The date must be within the next 14 days (not including the actual date).

Any ideas?

Thanks,

Shaun.

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your help. Formula works well - however, it is bringing through results where the date is less than the date in Sheet 1. The date must be within the next 14 days (not including the actual date).

Any ideas?

Thanks,

Shaun.

Control+shift+enter:

=IFERROR(INDEX(Sheet2!$C$2:$C$5,MIN(IF(Sheet2!$A$2:$A$5=$A2,IF((Sheet2!$B$2:$B$5>$B2)*(Sheet2!$B$2:$B$5<=$B2+14),ROW(Sheet2!$A$2:$C$5)-ROW(INDEX(Sheet2!$A$2:$C$5,1,1))+1,9.99E+307),9.99E+307))),"")

 

[ShaunF]

Thanks very much Aladin. Perfect.

 

[Aladin Akyurek]

Thanks very much Aladin. Perfect.

Shaun, you are welcome.