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).
<tbody>
</tbody>
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: