next date in list greater than cell value

tpowell1986

New Member
Joined
Jun 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
hi,

we have a list of products has have been reported lost, i want to look at the list of transactions on another sheet to see if there is any further transactions after the date it was reported lost?

thanks in advance

i.e.
VesselLost DateFurther TransactionsVesseldate_out
728516508/04/201520/06/2021728516501/01/2014
775961608/03/2018775961602/01/2014
775964508/12/2015775964503/01/2014
775972308/01/2018728516520/06/2021
775978908/08/2016728516523/06/2021
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEF
1VesselLost DateFurther TransactionsVesseldate_out
2728516508/04/201523/06/2021728516501/01/2014
3775961608/03/2018 775961602/01/2014
4775964508/12/2015 775964503/01/2014
5775972308/01/2018 728516520/06/2021
6775978908/08/2016 728516523/06/2021
Lists
Cell Formulas
RangeFormula
C2:C6C2=LET(Mx,MAXIFS(F:F,E:E,A2),IF(Mx>B2,Mx,""))
 

tpowell1986

New Member
Joined
Jun 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
thank you, it needs ideally to show the next date in the list so in this case cell f5 not the max date
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IFERROR(AGGREGATE(15,6,$F$2:$F$6/($E$2:$E$6=A2)/($F$2:$F$6>B2),1),"")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,063
Messages
5,704,064
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top