SOLVED

Formula to return a date within a range of dates

Steel Contributor

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
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

Hi Tony, please use XLOOKUP().

I have combined year and quarter for the correct result.

 

=XLOOKUP(YEAR([@[Payment Date]])*10+ROUNDUP(MONTH([@[Payment Date]])/3,0),ROUNDUP(YEAR(Table2[Conversion])*10+MONTH(Table2[Conversion])/3,0),Table2[Conversion],"",1)

 

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.

@Tony2021 

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],"")

 

worked perfectly! thank you very much for the help!
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

Hi Tony, please use XLOOKUP().

I have combined year and quarter for the correct result.

 

=XLOOKUP(YEAR([@[Payment Date]])*10+ROUNDUP(MONTH([@[Payment Date]])/3,0),ROUNDUP(YEAR(Table2[Conversion])*10+MONTH(Table2[Conversion])/3,0),Table2[Conversion],"",1)

 

View solution in original post