Return the higgest and lowest date

JOHN007

New Member
Joined
Aug 7, 2014
Messages
38
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).



Sheet1
A
B
RESULT()
Expected Result
1
Name
DATE
Nearest min date
Nearest max date
expectedmin date to be returned
expected date to be returned
2
Apple
01-01-2018
?
?
02-01-2018
NA
3
Apple
03-01-2018
?
?
03-01-2018
03-01-2018
4
Orange
08-01-2018
?
?
NA
09-01-2018
5
banana
11-01-2018
?
?
10-01-2018
12-01-2018
Sheet 2
A
B
Name
Date
1
Apple
02-01-2018
2
Apple
03-01-2018
3
Orange
07-01-2018
4
Orange
09-01-2018
5
Orange
10-01-2018
6
Banana
10-01-2018
7
Banana
12-01-2018
8
Banana
13-01-2018

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
L

Legacy 421546

Guest
Since dates are doubles you can subtract dates from sheet 1 with dates from sheet 2 and find the smallest / highest difference
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,109,306
Messages
5,527,912
Members
409,793
Latest member
shawnash

This Week's Hot Topics

Top