Return Number Based on Date

[dunmore83]

Hi,

In my 1st spreadsheet named ABC I have unique numbers (no duplicates) in Column B

In my 2nd spreadsheet named MOP I have dates in column F & numbers in Column G. In column B are the values from Column B in column B in the ABC spreadsheet (with duplicates).

In my 1st spreadsheet ABC I need a formula to go into column F that will return the 1st date found in the MOP spreadsheet in Column F based on the Lookup Value in Column B

I then need a formula to go in the ABC spreadsheet in column J that will return the next newest date which occurs after the one returned from the formula in Column F. I then need to repeat this formula by continuing to find the next newest date in column F of MOP.

Hope it makes sense!

Any helpers ??

 

[Peter_SSs]

I will try XL2BB next time.

OK, cheers.
What about this though?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Excellent. That works except in the 3rd column where this no 3rd date yet it returns #NUM! Can we adjust the formula so it returns a blank value if no 3rd date is found ?

Sure ..

=IFERROR(AGGREGATE(15,6,MOP!F$2:F$99/(MOP!B$2:B$99=B2),3),"")

 

[dunmore83]

Thanks so much

I need another version of that formula if you can please help. I need the formula to be able to search the same criteria but find the 2nd most recent date for each search value

So in the example worksheet you have posted the formula would return 04/Feb/22 for 12345 & 29/Jan/22 for 12346

 

[Peter_SSs]

I need the formula to be able to search the same criteria but find the 2nd most recent date for each search value

=AGGREGATE(14,6,MOP!F$2:F$100/(MOP!B$2:B$100=B2),2)