【函数初级201203班】练习5---查找函数练习【已开贴】

huanran2000

【函初201203班】-A21-HUANRAN2000
练习一：辅助列公式     =--LEFT(C3,FIND("~",C3)-1)
                                =VLOOKUP(G3,$B$2:$D$7,3)
练习二：=INDEX($C$23:$E$27,MATCH(I23,$B$23:$B$27,0),MATCH(H23,$C$22:$E$22,0))
练习三：{=INDEX(D38:D46,MATCH(H37&H38,B38:B46&C38:C46,0))}

zx_wl

【函初201203班]- A23-zx_wl
{=INDEX(D38:D46,MATCH(H37&H38,(B38:B46)&(C38:C46),0))}

daichaodaichao

[函初班201203]-B11-daichaodaichao
练习一：方法1. =VLOOKUP(F3,$A$2:$C$7,3,1)
        方法2. =LOOKUP(F3,$A$3:$C$7,$C$3:$C$7)
练习二：=INDEX($C$23:$E$27,MATCH(I23,B$23:B$27,0),MATCH         (H23,C$22:E$22,0))
练习三：方法1. =SUM((B38:B46=H37)*(C38:C46=H38))*D38:D46
        方法2. =SUMPRODUCT((B38:B46=H37)*(C38:C46=H38)*D38:D46)
        方法3.辅助列 =INDEX(D37:D46,MATCH(H37&H38,A37:A46,0))
        方法4.辅助列 =VLOOKUP(H37&H38,A38:D46,4,0)
        方法5.数组 =VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)

daichaodaichao

不好意思，因为还不太熟悉回复，所以回复重复了，抱歉！

新华人家

【函初201203班]- A06-新华人家
练习一：=LOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"})

练习二：{=OFFSET($B$18,MAX(($B$19:$B$23=J19)*ROW($19:$23))-ROW($B$18),MAX(($C$18:$E$23=H19)*COLUMN($C:$E))-COLUMN($B$18))}
            
             =SUMPRODUCT((B29:B37=H28)*(C29:C37=H29)*(D29:D37))

nuf2002

很是惭愧，在网上查阅了些资料，试着做了一下，虽然也出结果，但不知对不，也呈上请学委批改，谢谢。
一题：
1. A3中输入 =RIGHT(B3,(LEN(B3)-SEARCH("~~~",B3)))*1，下拉至A7，并使A3：C7按A列降序排列；
2. G3中输入 =OFFSET($A$2,MATCH(F3,$A$3:$A$7,-1),2)，下拉。
二题：  在j23中输入：=VLOOKUP(I23,$B$23:$E$27,MATCH(H23,$B$22:$E$22,),0)，下拉。
三题：
1.  在A38中输入 =B38&"|"&C38；
2.  在h39中输入 =OFFSET($A$37,MATCH($H$37&"|"&$H$38,$A$38:$A$46,),3)。

【函初201203班]- C05-nuf2002

xxdd6

练习一：

1. =IF(F3>=90,"A",IF(F3>=80,"B",IF(F3>=70,"C",IF(F3>=40,"D","F"))))

复制代码

练习二：

1. =SUMPRODUCT(($C$22:$E$22=H23)*($B$23:$B$27=I23),$C$23:$E$27)

复制代码

552314637

[函数初级201203班]-B34-552314637

练习一、=IF(F3<40,"F",IF(F3<70,"D",IF(F3<80,"C",IF(F3<90,"B","A"))))
练习二、=OFFSET($B$22,MATCH(I23,$B$23:$B$27,0),MATCH(H23,$C$22:$E$22,0))
练习三、=VLOOKUP(H37&H38,IF({1,0},$B$38:$B$46&$C$38:$C$46,$D$38:$D$46),2,0)

芒果冰激凌

【函初201203班]- C22-芒果冰激凌

1. =LOOKUP(F3,{0,40,70,80,90},{"F","D","C","B","A"})

复制代码

1. =OFFSET(C$22,MATCH(I23,B$23:B$27,),MATCH(H23,C$22:E$22,)-1)

复制代码

数组

1. =IFERROR(VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,),"部门姓名不匹配")

复制代码

augustzhu1983

【函初201203班】-B29-augustzhu1983
练习一

1. =LOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"})

复制代码

1. =TEXT(TEXT(F3,"[>=90]A;[>=80]!B;0"),"[>=70]C;[>=40]!D;F")

复制代码

1. =INDEX({"F";"F";"F";"D";"D";"D";"C";"B";"A";"A"},F3/10)

复制代码

练习二
一

1. =VLOOKUP(I23,B$22:E$27,MATCH(H23,B$22:E$22,),)

复制代码

1. =HLOOKUP(H23,B$22:E$27,MATCH(I23,B$22:B$27,),)

复制代码

1. =INDEX(C$23:E$27,MATCH(I23,B$23:B$27,),MATCH(H23,C$22:E$22,))

复制代码

1. =OFFSET(B$22,MATCH(I23,B$23:B$27,),MATCH(H23,C$22:E$22,))

复制代码

1. =INDIRECT("r"&ROW(B$22)+MATCH(I23,B$23:B$27,)&"c"&COLUMN(B$22)+MATCH(H23,C$22:E$22,),)

复制代码

1. =SUM((C$22:E$22=H23)*(B$23:B$27=I23)*C$23:E$27)

复制代码

二

1. =INDEX(D38:D46,MATCH(H37&H38,B38:B46&C38:C46,))

复制代码

1. =VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,)

复制代码

1. =SUM((B38:B46=H37)*(C38:C46=H38)*D38:D46)

复制代码

1. =SUMIFS(D38:D46,B38:B46,H37,C38:C46,H38)

复制代码