How to return the last n number of values corresponding to a specific category?-CodePudding

I have the following sample data.

Date         Category   Price   Quantity
02-01-2019  BASE_Y-20   279 1
02-01-2019  BASE_Y-21   271.25  0
03-01-2019  BASE_Y-20   276.5   2
03-01-2019  BASE_Y-21   266.5   0
04-01-2019  BASE_Y-20   272.88  14
04-01-2019  BASE_Y-21   266.5   1
07-01-2019  BASE_Y-20   270.48  29
07-01-2019  BASE_Y-21   262.75  0
08-01-2019  BASE_Y-20   270 4
08-01-2019  BASE_Y-21   264 0
09-01-2019  BASE_Y-20   270.06  31
09-01-2019  BASE_Y-21   262.85  0

What is a dynamic formula that I can use to return the last 5 prices corresponding to category BASE_Y-20 ? The formula must return whatsoever prices are available, if 5 values are not present, which is the challenging part. (Eg: For the given data, 270.06, 270, 270.48, 272.88 and 276.5 must be returned. If we only had 1st row, it must return 279)

I have tried sumproduct. That of course gives the corresponding prices. Offset can be availed to get last 5 data. But no way for getting last 5 prices corresponding to a specific category that is dynamic.

CodePudding user response：

Last Matches From Bottom to Top

EDIT

With great help from P.b, the formula got reduced to the following:

=LET(cData,B2:B13,rData,C2:C13,cStr,G1,rCount,G2,
    rFiltered,IFERROR(TAKE(TAKE(FILTER(HSTACK(cData,rData),cData=cStr),,-1),-rCount),""),
Result,SORTBY(rFiltered,SEQUENCE(ROWS(rFiltered)),-1),Result)

Screenshot Formulas

J2  =HSTACK(B2:B13,C2:C13)
L2  =FILTER(J2#,B2:B13=G1)
N2  =TAKE(L2#,,-1)
O2  =TAKE(N2#,-G2)
P2  =ROWS(O2#)
Q2  =SEQUENCE(P2)
R2  =SORTBY(O2#,Q2#,-1)

Issues in the Initial Post

I'm not sure what drove me to the decision that the data is A3:D13 when it is obviously B3:B13 and C3:C13.
TAKE will work if there are fewer rows/columns than asked for i.e. if you need five rows and there are only two, two will be returned.
Instead of using ROWS with the SEQUENCE function and then using it with INDEX, it is simpler to use SORTBY to sort by the sequence, in this particular case descending (-1).

Initial Post (Bad)

LET

=LET(Data,A2:D13,cCol,2,cStr,G1,rCol,3,rCount,G2,
    cData,INDEX(Data,,cCol),rData,INDEX(Data,,rCol),Both,HSTACK(cData,rData),
    bFiltered,FILTER(Both,cData=cStr),rFiltered,TAKE(bFiltered,,-1),rRows,ROWS(rFiltered),
    fRows,IF(rRows>rCount,rCount,rRows),rSequence,SEQUENCE(fRows,,rRows,-1),
Result,INDEX(rFiltered,rSequence),Result)

Screenshot Formulas

J3  =INDEX(A2:D13,,2)
K3  =INDEX(A2:D13,,3)
L3  =HSTACK(J3#,K3#)
N3  =FILTER(L3#,J3#=G1)
P3  =TAKE(N3#,,-1)
Q3  =ROWS(P3#)
R3  =IF(Q3>G2,G2,Q3)
S3  =SEQUENCE(R3,,Q3,-1)
T3  =INDEX(P3#,S3#)

CodePudding user response：

You can try:

Formula in F3:

=TAKE(SORT(FILTER(A:C,B:B=F1),1),-F2,-1)

Few notes:

The latest price will be at the bottom;
If your data is always sorted to begin with, just ditch the nested SORT() and use =TAKE(FILTER(A:C,B:B=F1),-F2,-1);
If no value is present at all, nest the formula in an =IFERROR(<Formula>,"") to return any value you'd like to display in such event.