【求助】如何提取满足条件的最新日期对应的数据

小雨点qin · 发表于 2024-3-22 00:55

如何在满足名称和类型的条件下，提取A列中最新日期对应的数据？

swl4561255 · 发表于 2024-3-22 05:58

Sub test()
ar = Range("A1").End(4).Row
arr = Range("A2:E" & ar)
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To ar - 1
d(arr(i, 2) & arr(i, 3)) = arr(i, 4) & "," & arr(i, 5)
Next
For j = 2 To Range("G1").End(4).Row
s = d(Cells(j, "G") & Mid(Cells(1, "H"), 3, 4))
Cells(j, "H") = Split(s, ",")(0)
Cells(j, "I") = Split(s, ",")(1)
Next
Set d = Nothing
End Sub

复制代码

xlllw203 · 发表于 2024-3-22 08:02

=INDEX(D:D,RIGHT(MAX(IF(($G2=$B$2:$B$22)*(MID(H$1,3,4)=$C$2:$C$22),$A$2:$A$22/1%%+ROW($2:$22))),3))

复制代码

edwin11891 · 发表于 2024-3-22 08:12

H2:
=Iferror(max(filter(A$2:A$100,(B$2:B$100=G2)*(C$2:C$100="A001")),"")

I2:
=Iferror(filter(E$2:E$100,(B$2:B$100=G2)*(C$2:C$100="A001")*(A$2:A$100=H2)),"")

天有多高999 · 发表于 2024-3-22 08:23

=LOOKUP(1,0/(FIND($C$2:$C$13,H$1)*($B$2:$B$13=$G2)),D$2:D$13)

boyuanoffice · 发表于 2024-3-22 08:55

本帖最后由 boyuanoffice 于 2024-3-22 09:11 编辑

=LET(nm,G2,
tp,TEXTBEFORE(TEXTAFTER($H$1,"类型"),CHAR(10)),
dt,FILTER($B$2:$E$2000,$C$2:$C$2000=tp),
XLOOKUP(nm,TAKE(dt,,1),TAKE(dt,,-2),"",,-1))

复制代码

我要成为专家 · 发表于 2024-3-22 09:15

H2,下拉右拉

=LOOKUP(,0/FREQUENCY(-9^9,-($B$2:$B$13=$G2)*($C$2:$C$13="A001")*$A$2:$A$13),D$2:D$13)

复制代码

无关岁月 · 发表于 2024-3-22 09:29

=XLOOKUP(NOW(),IF(($B$2:$B$13=$G2)*($C$2:$C$13="A001"),$A$2:$A$13),D$2:D$13,,-1)

复制代码

wdy895 · 发表于 2024-3-22 10:10

=XLOOKUP($G2&"A001",$B:$B&$C:$C,D:D,,,-1)

复制代码

苍域 · 发表于 2024-3-22 10:15

=INDEX($D$2:$D$13,MATCH(MAX(IF(($B$2:$B$13=$G2)*($C$2:$C$13="A001"),$A$2:$A$13,"")),IF(($B$2:$B$13=$G2)*($C$2:$C$13="A001"),$A$2:$A$13,""),0))

		自动登录	找回密码
密码			免费注册

[求助] 【求助】如何提取满足条件的最新日期对应的数据