又有不会的了，多条件取值

13630703332

求老师们指导

solextrade

=VLOOKUP(I2,A:E,(H2="S11")*2+(G2="铝")+2,)

quqiyuan

=INDEX(B3:E7,MATCH(I2,A3:A7,),MATCH(H2,$B$1:$E$1,)+MATCH(G2,{"铜","铝"},)-1)

fsw0812

=SUMPRODUCT((OFFSET(A1,1,MATCH(H2,B1:E1,),1,2)=G2)*(A3:A7=I2)*OFFSET(A1,2,MATCH(H2,B1:E1,),5,2))

part98

=INDEX(OFFSET($A$3:$A$7,,MATCH(H2,$B$1:$E$1,0),,2),MATCH(I2,$A$3:$A$7,0),MATCH(G2,$B$2:$C$2,0))

hxj119

=OFFSET(A2,MATCH(I2,A3:A7,0),2*(H2="S11")+2^(G2="铝"))

浮生若梦~~~

=SUMPRODUCT((LOOKUP(COLUMN(B:E),COLUMN(B:E)/(B1:E1>0),B1:E1)&B2:E2=H2&G2)*(A3:A7=I2)*B3:E7)

mengrs

=SUMPRODUCT((B$2:E$2=G2)*(LOOKUP(COLUMN(B:E),COLUMN(B:E)/(B$1:E$1<>""),B$1:E$1)=H2)*(A$3:A$7=I2)*B$3:E$7)

zhangcheng6688

学习一下数组，打扰了
Sub test()
arr = [g1].CurrentRegion
brr = [b1].CurrentRegion
For i = 2 To UBound(brr, 2)
   If brr(1, i) = "" Then
      brr(1, i) = brr(1, i - 1)
   End If
Next
For i = 2 To UBound(arr)
   For j = 1 To UBound(brr)
      For k = 2 To UBound(brr, 2)
         If arr(i, 1) = brr(2, k) And arr(i, 2) = brr(1, k) And arr(i, 3) = brr(j, 1) Then
            arr(i, 4) = brr(j, k)
         End If
      Next
   Next
Next
[g1].CurrentRegion = arr
End Sub
代码写得比较别扭，在处理合并单元格的时候

明象

=MAX(IF(MID(H2,2,2)*1=11,{0,0,1,1},{1,1,0,0})*(B2:E2=G2)*IF(A2:A7=I2,B2:E7))