收集一下,以备不时只需。
=VLOOKUP(G2&H2,IF({1,0},A2:A7&C2:C7,B2:B7),2,)
=VLOOKUP($G$2&$H$2,CHOOSE({1,2,3},$A$2:$A$7&$C$2:$C$7,$B$2:$B$7,$D$2:$D$7),COLUMN(B1),)
=VLOOKUP(H2,IF(A2:A7=G2,C2:D7,0),2,)
=HLOOKUP(G2&H2,IF({1;0},TRANSPOSE(A2:A7&C2:C7),TRANSPOSE(B2:B7)),2,)
=HLOOKUP($G2&$H2,CHOOSE({1;2;3},TRANSPOSE($A2:$A7&$C2:$C7),TRANSPOSE($B2:$B7),TRANSPOSE($D2:$D7)),COLUMN(B1),)
=HLOOKUP(H2,TRANSPOSE(IF(A2:A7=G2,C2:D7,0)),2,)
=LOOKUP(MATCH(G2&H2,A2:A7&C2:C7,),ROW(1:6),B2:B7)
=LOOKUP(1,0/(A2:A7&C2:C7=G2&H2),B2:B7)
=LOOKUP(2,1/(A2:A7&C2:C7=G2&H2),B2:B7)
=LOOKUP(2,1/((A2:A7=G2)*(C2:C7=H2)),B2:B7)
=INDEX(B2:B7,MATCH(G2&H2,A2:A7&C2:C7,))
=INDEX(B2:B7,MATCH(1,(A2:A7=G2)*(C2:C7=H2),))
=INDEX(B2:B7,MATCH(TRUE,A2:A7&C2:C7=G2&H2,))
=INDEX(B2:B7,MAX(IF(A2:A7&C2:C7=G2&H2,ROW(1:6))))
=CHOOSE(MATCH(G2&H2,A2:A7&C2:C7,),B2,B3,B4,B5,B6,B7)
=CHOOSE(MATCH(1,(A2:A7=G2)*(C2:C7=H2),),B2,B3,B4,B5,B6,B7)
=CHOOSE(MATCH(TRUE,A2:A7&C2:C7=G2&H2,),B2,B3,B4,B5,B6,B7)
=CHOOSE(MAX(IF(A2:A7&C2:C7=G2&H2,ROW(1:6))),B2,B3,B4,B5,B6,B7)
转载于:https://blog.51cto.com/kirin/359095