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

pang1688

【函初201203班】-c30-pang1688:
1、=IF(F3<=40,HLOOKUP($C$2,$C$2:$C$7,2),IF(F3<=70,HLOOKUP($C$2,$C$2:$C$7,3),IF(F3<=80,HLOOKUP($C$2,$C$2:$C$7,4),IF(F3<=90,HLOOKUP($C$2,$C$2:$C$7,5),IF(F3<=100,HLOOKUP($C$2,$C$2:$C$7,6),"")))))
2、=SUM(VLOOKUP(I23,$B$23:$E$27,MATCH(H23,$C$22:$E$22,0)+1))
3、{=SUM((B38:B46&C38:C46=H37&H38)*(D38:D46))}

tbcdmp

【函初201203班】-D13-tbcdmp
第三个公式
=VLOOKUP(H38,$C$38:$D$46,2,0)
其他的还在研究

rlz680926

2003中试过
1数组公式{=VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)}
2求和
=SUMPRODUCT((H37=B38:B46)*(H38=C38:C46)*D38:D46)

MYHERE131

题一：LOOKUP(F3,{0,40,70,80,90;"F","D","C","B","A"})
题二：INDIRECT("r"&MATCH($I23,$B$23:$B$27,0)+22&"c"&MATCH($H23,$C$22:$E$22,0)+2,)
【函初201203班】-D08-MYHERE131

MYHERE131

题一：LOOKUP(F3,{0,40,70,80,90;"F","D","C","B","A"})
题二：INDIRECT("r"&MATCH($I23,$B$23:$B$27,0)+22&"c"&MATCH($H23,$C$22:$E$22,0)+2,)
VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)  三键返回
【函初201203班】-D08-MYHERE131

yjeeee

【函初201203班】- E28 - yjeeee
查找1：=LOOKUP(F3,{0,40,70,80,90;"F","D","C","B","A"})
查找2：=INDEX($C$23:$E$27,MATCH(I23,$B$23:$B$27,0),MATCH(H23,$C$22:$E$22,0))
查找3（数组）：{=INDEX(D38:D46,MATCH(H37&H38,B38:B46&C38:C46,0),1)}

涧水长流

【函初201203班】- A17-涧水长流
练习一：=LOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"})
=VLOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"},2)
练习二：1，=VLOOKUP($I23,$B$22:$E$27,MATCH($H23,$B$22:$E$22,),0)
2，=OFFSET($B$37,MATCH($H$38,$C$38:$C$46,0),2,1,1)

蓝火雨

【函初201203班]- B01-蓝火雨

=LOOKUP(F3,{0,40,70,80,90},$C$3:$C$7)

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

=VLOOKUP($H$37&$H$38,IF({1,0},$B$38:$B$46&$C$38:$C$46,$D$38:$D$46),2,0)输入完后按三键回车CTRL+ALT+ENTER

xuyouxm

1. 【函初201203班]- A01-xuyouxm
   
2. 
   
3. =IF(F3>=90,$C$7,IF(F3>=80,$C$6,IF(F3>=70,$C$5,IF(F3>=40,$C$4,$C$3))))

复制代码

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

复制代码

1. =VLOOKUP(H38,C37:D46,2,0)

复制代码

xiaobai83

１、=VLOOKUP(H38,C38:D46,2,0)
２、=LOOKUP(H38,C38:D46)