求最新入库、出库日期

陆缊

求助大神：求最新入库出库日期。

博爱风雨同舟

=INDEX(A:E,SMALL(IF(MAXIFS(D3:D16,A3:A16,A3:A16,B3:B16,B3:B16,C3:C16,{"入库","出库"},E3:E16,E3:E16)=D3:D16,ROW(3:16),""),ROW(1:11)),COLUMN(A:E))

yfan17

1. =IFERROR(AGGREGATE(14,6,($D$3:$D$16)/($A$3:$A$16=$I3)/($B$3:$B$16=$J3)/($E$3:$E$16=$K3)/($C$3:$C$16="入库"),1),"")

复制代码

1. =IFERROR(AGGREGATE(14,6,($D$3:$D$16)/($A$3:$A$16=$I3)/($B$3:$B$16=$J3)/($E$3:$E$16=$K3)/($C$3:$C$16="出库"),1),"未出库")

复制代码

jivy1212

1. =IFERROR(1/LOOKUP(,0/FREQUENCY(0,9^9-($A$2:$A$16&$B$2:$B$16&$E$2:$E$16=$I3&$J3&$K3)*N(+$D$2:$D$16)*($C$2:$C$16=MID(L$2,5,2))),$D$2:$D$16)^-1,"无")

复制代码

右拉下拉

博爱风雨同舟

M3=UNIQUE(TOROW(INDEX(D:D,IF(MAXIFS($D$3:$D$16,$A$3:$A$16,J3,$B$3:$B$16,K3,$C$3:$C$16,{"入库","出库"},$E$3:$E$16,L3)=$D$3:$D$16,ROW($3:$16),0/0)),2,1),1)

2021小门徒

日期的本质是数值（序列值），多条件求最大，MAXIFS 的本职工作：

最近一次入库日期
L2=MAXIFS($D$3:$D$16,$A$3:$A$16,$I3,$B$3:$B$16,$J3,$E$3:$E$16,$K3,$C$3:$C$16,"入库")

最近一次出库日期
M2=IF(MAXIFS($D$3:$D$16,$A$3:$A$16,$I3,$B$3:$B$16,$J3,$E$3:$E$16,$K3,$C$3:$C$16,"出库")=0,"未出库",MAXIFS($D$3:$D$16,$A$3:$A$16,$I3,$B$3:$B$16,$J3,$E$3:$E$16,$K3,$C$3:$C$16,"出库"))

alan57

1. =LET(_a,FILTER($C$3:$D$16,$A$3:$A$16&$B$3:$B$16&$E$3:$E$16=I3&J3&K3),XLOOKUP({"入库","出库"},TAKE(_a,,1),TAKE(_a,,-1),"未出库",,-1))

复制代码

一杯绿茶来了

=LET(s,MAXIFS($D:$D,$B:$B,$J3,$A:$A,$I3,$C:$C,MID(L$2,5,2),$E:$E,$K3),IF(s>0,s,"未出库"))