Get the value from the second last occurrence

[Muthukumar Rajaram]

Good day everyone,

I have a set of data where a combination (product/location/batch number) reoccurs. When I extend the dataset with latest information (number of days left to sell for a batch), I need to get the comment based on my notes from the last time I had reviewed the combination.

Example:

Feb-23 notes
A-xyz-123 - Will be sold to Costco by mid April
Mar-23 notes
A-xyz-123 - comments section is blank (because I didn’t have time
In April-23, when I paste the combination 100 Rows below the above, I need the comment from Feb-23 “Will be sold to Costco by mid April” to appear against this.

Hope this makes sense.
Thanks for your help.
Cheers

 

[vletm]

Muthukumar Rajaram
With a sample Excel-file (with realistic sample data) would have more hope with this.

 

[Peter Bartholomew]

This seems to do what is asked for, but there must be a simpler way, and one that does not require Excel 365.

=BYROW(
    table,
    LAMBDA(thisRow,
        LET(
            currentProduct, (thisRow product),
            currentComment, (thisRow comment),
            partListProducts, product1:currentProduct,
            partListComments, comment1:currentComment,
            listOfMatches, FILTER(
                partListComments,
                partListProducts = currentProduct,
                "not found"
            ),
            IFERROR(CHOOSEROWS(listOfMatches, -2), "None found")
        )
    )
)

 

[Peter Bartholomew]

A little less in the way of sophistication!

= INDEX(comment,
    MAXIFS(serial,
      serial, "<"&serial,
      product, product
    )
  )