Return the higgest and lowest date

[JOHN007]

I need help to look for nearest date (Min, Max) available in sheet2 for the given date sheet1 and applicable variable(Apple). (similar to lookup but in this case exat date doesn't match).



[TABLE="width: 690"]
<tbody>[TR]
[TD]Sheet1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD="colspan: 2"]RESULT()
[/TD]
[TD="colspan: 2"]Expected Result
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]DATE
[/TD]
[TD]Nearest min date
[/TD]
[TD]Nearest max date
[/TD]
[TD]expectedmin date to be returned
[/TD]
[TD]expected date to be returned
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Apple
[/TD]
[TD]01-01-2018
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]02-01-2018
[/TD]
[TD]NA
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Apple
[/TD]
[TD]03-01-2018
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]03-01-2018
[/TD]
[TD]03-01-2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Orange
[/TD]
[TD]08-01-2018
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]NA
[/TD]
[TD]09-01-2018
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]banana
[/TD]
[TD]11-01-2018
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]10-01-2018
[/TD]
[TD]12-01-2018
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Apple
[/TD]
[TD="align: right"]02-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Apple
[/TD]
[TD="align: right"]03-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Orange
[/TD]
[TD="align: right"]07-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Orange
[/TD]
[TD="align: right"]09-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD]Orange
[/TD]
[TD="align: right"]10-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD]Banana
[/TD]
[TD="align: right"]10-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD]Banana
[/TD]
[TD="align: right"]12-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD]Banana
[/TD]
[TD="align: right"]13-01-2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 421546]

Since dates are doubles you can subtract dates from sheet 1 with dates from sheet 2 and find the smallest / highest difference