Allocating / Apportioning Challan Amounts to Different Receipts

KanwalNo1 If you're still interested in a formula-based solution, PeterBartholomew1 's method is the way to go (with a few minor tweaks). By using the row numbers of each dataset for the Input and Output IDs, as opposed to the dates, you can use the INDEX function to pull the necessary columns from the original dataset and, with a few additional steps, generate the desired results.

The core elements of the following formula were taken from Peter's custom FIFO functions and customized to meet your specific needs:

=LET(
    FIFO, LAMBDA(inAmt,outAmt,
        LET(
            inRows, ROWS(inAmt) + 1,
            outRows, ROWS(outAmt) + 1,
            inRecords, HSTACK(EXPAND(SEQUENCE(inRows),, 2, ""), VSTACK(0, SCAN(0, inAmt, LAMBDA(a,v, a + v)))),
            outRecords, CHOOSECOLS(HSTACK(EXPAND(SEQUENCE(outRows),, 2, ""), VSTACK(0, SCAN(0, outAmt, LAMBDA(a,v, a + v)))), 2, 1, 3),
            unionAll, SORT(VSTACK(inRecords, outRecords), 3),
            inId, SCAN(1, CHOOSECOLS(unionAll, 1), LAMBDA(a,v, IF(v = "", a, v))),
            outId, SCAN(1, CHOOSECOLS(unionAll, 2), LAMBDA(a,v, IF(v = "", a, v))),
            amt, CHOOSECOLS(unionAll, 3),
            netAmt, VSTACK(DROP(amt, 1), TAKE(amt, -1)) - amt,
            total, SCAN(0, netAmt, LAMBDA(a,v, a + v)),
            FILTER(HSTACK(inId, outId, netAmt, total), (outId < outRows) * (netAmt <> 0))
        )
    ),
    chaData, SORT(FILTER(Challan, Challan[Amount] > 0), {1,3}),
    recData, SORT(FILTER(Receipts, Receipts[Amount] > 0), {1,4,3}),
    chaID, CHOOSECOLS(chaData, 1),
    recID, CHOOSECOLS(recData, 1),
    REDUCE(
        {"ID","Party","Receipt Date","Amount","Tax Amt","Running Total","CIN","CIN Date"},
        UNIQUE(recID),
        LAMBDA(p,v,
            LET(
                cha, FILTER(chaData, chaID = v, EXPAND(v,, COLUMNS(chaData), 0)),
                rec, FILTER(recData, recID = v),
                output, FIFO(CHOOSECOLS(cha, 4), CHOOSECOLS(rec, 5)),
                recOut, INDEX(rec, CHOOSECOLS(output, 2), {1,3,4}),
                amount, CHOOSECOLS(output, 3),
                taxAmt, ROUND(amount * 0.001, 0),
                runTtl, CHOOSECOLS(output, 4),
                cId, CHOOSECOLS(output, 1),
                chaOut, IF(cId > ROWS(cha), {"Short",""}, INDEX(cha, cId, {2,3})),
                VSTACK(p, HSTACK(recOut, amount, taxAmt, runTtl, chaOut))
            )
        )
    )
)

This method is far more efficient than my previous attempts to iterate through the records with SCAN and LAMBDA recursion, which suffered from significant calculation lag after just 100-200 rows of receipts:

=LET(
    cha, SORT(FILTER(Challan, Challan[Amount] > 0), {1,3}),
    chaID, CHOOSECOLS(cha, 1),
    chaAmt, CHOOSECOLS(cha, 4),
    rws, ROWS(cha),
    rec, SORT(FILTER(Receipts, Receipts[Amount] > 0), {1,4,3}),
    recID, CHOOSECOLS(rec, 1),
    recAmt, CHOOSECOLS(rec, 5),
    FIFO, LAMBDA(ME,text,
        LET(
            p, TAKE(--TEXTSPLIT(text, "|"),, -6),
            prr, INDEX(p, 1),
            pra, INDEX(p, 2),
            pcr, INDEX(p, 3),
            pca, INDEX(p, 4),
            pur, INDEX(p, 5),
            rr, prr + NOT(pra),
            ra, IF(pra, pra, INDEX(recAmt, rr)),
            rId, INDEX(recID, rr),
            new, OR(NOT(pca), rId <> IF(pcr, INDEX(chaID, pcr), "")),
            cr, IF(new, IF(pur = rws, 0, IFNA(XMATCH(rId, DROP(chaID, pur)) + pur, 0)), pcr),
            ca, IF(new, IF(cr, INDEX(chaAmt, cr), 0), pca),
            amt, IF(ca, MIN(ra, ca), ra),
            txt, IF(rr = prr, text & "|", "") & TEXTJOIN("|",, rr, ra - amt, cr, MAX(ca - amt, 0), MAX(cr, pur), amt),
            IF(amt = ra, txt, ME(ME, txt))
        )
    ),
    TEXT2COLS, LAMBDA(array,delimiter,
        LET(
            arr, delimiter & array & delimiter,
            cols, SEQUENCE(, MAX(LEN(arr) - LEN(SUBSTITUTE(arr, delimiter, ))) / LEN(delimiter) - 1),
            TEXTBEFORE(TEXTAFTER(arr, delimiter, cols), delimiter)
        )
    ),
    results, SCAN(TEXTJOIN("|",, SEQUENCE(1,6,0,0)), SEQUENCE(ROWS(rec)), LAMBDA(a,v, FIFO(FIFO, a))),
    output, WRAPROWS(--TOCOL(TEXT2COLS(results, "|"), 2), 6),
    recOut, INDEX(rec, CHOOSECOLS(output, 1), {1,3,4}),
    amount, CHOOSECOLS(output, 6),
    taxAmt, ROUND(amount * 0.001, 0),
    runTtl, --TEXTAFTER(SCAN("|", CHOOSECOLS(recOut, 1) & "|" & amount, LAMBDA(a,v, LET(
        id, TEXTBEFORE(v, "|"),
        amt, TEXTAFTER(v, "|"),
        id & "|" & IF(id = TEXTBEFORE(a, "|"), TEXTAFTER(a, "|") + amt, amt)))), "|"),
    cId, CHOOSECOLS(output, 3),
    chaOut, IF(cId, INDEX(cha, cId, {2,3}), {"Short",""}),
    HSTACK(recOut, amount, taxAmt, runTtl, chaOut)
)

Enjoy!