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

  • =@IFERROR(SORTBY(_Tbl2[Transaction2_Date]/(_Tbl2[ID]=[@ID]),ABS(_Tbl2[Transaction2_Date]-[@[Transaction1_Date]])),NA())

    enter image description here

    PS you can't use table names TBL1 and TBL2 since they're existing names for cell ranges (Column TBL row 1 and 2), therefore I used _Tbl1 and _Tbl2 instead.

    Or for older Excel version: =INDEX(_Tbl2[Transaction2_Date],MATCH(AGGREGATE(15,6,ABS(_Tbl2[Transaction2_Date]-[@[Transaction1_Date]])/(_Tbl2[ID]=[@ID]),1),ABS(_Tbl2[Transaction2_Date]-[@[Transaction1_Date]])/(_Tbl2[ID]=[@ID]),0))