求助大神，根据姓名学科查找成绩

雅之希希

感谢各位大神的帮助，向你们学习

我的城市你来过

=LET(a,DROP(REDUCE("",$H$1:$M$1,LAMBDA(x,y,VSTACK(x,BYROW(FILTER($H$2:$M$6&"@",$H$1:$M$1=y,""),CONCAT)&y))),1),TEXTJOIN(",",,UNIQUE(REGEXP(FILTER(a,REGEXP(a,"@"&A2&"@"&B2,1)),"\d+"))))

菜狗一只

1. =TEXTJOIN(",",TRUE,LET(X,MATCH(B2,$F$1:$L$1,0),FILTER(INDEX($F$2:$L$6,,X),INDEX($F$2:$L$6,,X+1)=A2)))

复制代码

你是那块小饼干

=TEXTJOIN(",",TRUE,CHOOSECOLS(IF($G$2:$K$6=A2,$F$2:$J$6,""),MATCH(B2,$F$1:$K$1,0)))

守候_CJ

Office 365，C2公式：
=LET(a,FILTER($F$2:$K$6,$F$1:$K$1=B2,""),b,INDEX(a,,2),c,INDEX(a,,1),d,FILTER(c,b=A2,""),TEXTJOIN("、",TRUE,d))

zhangcheng6688

学习一下字典
Sub test()
arr = [e1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 3 To UBound(arr, 2) Step 2
   For j = 2 To UBound(arr)
      strkey = arr(j, i) & arr(1, i)
      If Not d.exists(strkey) Then
         d(strkey) = arr(j, i - 1)
      Else
         d(strkey) = d(strkey) & "," & arr(j, i - 1)
      End If
   Next
Next
brr = [a1].CurrentRegion
For i = 2 To UBound(brr)
   strkey = brr(i, 1) & brr(i, 2)
   brr(i, 3) = d(strkey)
Next
Range("c2:c10000").ClearContents
[a1].CurrentRegion = brr
End Sub