Forum Discussion
Solved
Formula to return a date within a range of dates
Hello Experts,
I have a table with a payment date in column A.
I need to return a date found in a named range "Conversion" if that conversion date is in the same quarter that the payment date is in and if there is no corresponding conversion date then take the next highest date as found in the conversion table.
It's a little difficult to put into words.
the attached excel can better explain my desired outcome.
I am using a vlookup formula but it doesnt return the correct date in every case.
Grateful for the help. Let me know if there are any questions.
thank you
4 Replies
- thats amazing. thank you for the response.
I have a follow up though.
I am now backtracking a bit since I notice that the missing quarter was an overlook on my part. I now see that the Q1 2023 is present.
How could the formula be modified to return "" instead of the next quarter? I studied the formula but it way out of my league.
thank you very much.You only have to delete the 5th parameter. The default here is the exact search. You can simply remove the parameter ",1".
=XLOOKUP(YEAR([@[Payment Date]])*10+ROUNDUP(MONTH([@[Payment Date]])/3,0),ROUNDUP(YEAR(Table2[Conversion])*10+MONTH(Table2[Conversion])/3,0),Table2[Conversion],"")
