公式写了两天了，实在写不出来。

knnrsn

十分感谢您

dambcer

1. =IFNA(LOOKUP(1,0/(($A$2:$A$8=I4)*($B$2:$B$8=J4)*($D$2:$D$8<=K4)*($E$2:$E$8>=K4)),$C$2:$C$8),"")

复制代码

橒♂蝣

=LOOKUP(K2,FILTER(D:D,B:B=J2),FILTER(C:C,B:B=J2))

excel玉米

Index+match

橒♂蝣

=LOOKUP(K2,FILTER($D$2:$D$8,$B$2:$B$8=J2),FILTER($C$2:$C$8,$B$2:$B$8=J2))

limonet

有几万条，可以用用SQL

limonet

Sub Limonet()
    Dim StrSQL$, Cn As Object
    Set Cn = CreateObject("ADODB.connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
    StrSQL = "SELECT a.培训班名称 FROM [Sheet1$A:E]a Right Join [Sheet1$I:K]b On a.学号=b.学号 and a.姓名=b.姓名 and b.分数>=a.及格分数 and b.分数<=a.满分"
    Range("L2").CopyFromRecordset Cn.Execute(StrSQL)
End Sub

1786024734

1. =FILTER(C:C,(B:B=J2)*(D:D<=K2)*(E:E>=K2),"")

复制代码

knnrsn

思路又开阔了，感谢各位大佬。
我这就快马加鞭学习。

不动如山马卡龙

=XLOOKUP(1,($A$2:$A$8=I2)*($D$2:$D$8<=K2)*($E$2:$E$8>=K2),C2:C8,"")