Search code examples
excelexcel-formula

Closest date based on 1st transaction date, Mutiple criteria in Excel

I have 2 tables, 1 with TransictionDate_1 & 2nd table with TransictionDate_2. Both tables contain duplicate IDs.

I need to match TransictionDate_2 which nearest occurrence after TransictionDate_1. I have tried few Array and ABS formulas but getting inconsistence result. Below tables for more explanation.

enter image description here

Solution

  • Try using the following formula, based on ones Excel Version may required to hit CTRL+SHIFT+ENTER while exiting the edit mode.

    enter image description here

    =IFERROR(INDEX(Tbl_2[Transaction2_Date],
 MATCH(1,1/(([@ID]=Tbl_2[ID])*([@[Transaction1_Date]]<Tbl_2[Transaction2_Date])),-1)),"NA")

    Or, if you have access to MS365 then do the following:

    enter image description here

    =IFERROR(MIN(FILTER(Tbl_2[Transaction2_Date],(([@ID]=Tbl_2[ID])*([@[Transaction1_Date]]<Tbl_2[Transaction2_Date])))),"NA")