Nov 26 2022 09:30 AM
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
Nov 26 2022 10:01 AM
SolutionHi 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)
Nov 26 2022 02:42 PM
Nov 26 2022 03:01 PM
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],"")
Nov 26 2022 05:27 PM
Nov 26 2022 10:01 AM
SolutionHi 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)