Excel查询函数之无所不能的VLOOKUP(LOOKUP)实例介绍
世上查询数据没有什么是VLOOKUP解决不了的,如果有就再加上LOOKUP。
1、根据*精确查找俗称
=VLOOKUP(D3,A:B,2,0)
=LOOKUP(1,0/($A$2:$A$14=D2),$B$2:$B$14)
2、根据*精确查找俗称,并屏蔽错误值
=IFERROR(VLOOKUP(D2,A:B,2,0),"")
=IFERROR(LOOKUP(1,0/($A$2:$A$5=D2),$B$2:$B$5),"")
3、根据*按顺序精确查找俗称与订单数
=VLOOKUP($A13,$A$1:$F$10,COLUMN(),0)
=LOOKUP(1,0/($A$2:$A$10=$A13),B$2:B$10)
4、根据*不按顺序精确查找俗称与订单数
=VLOOKUP($A13,$A$1:$F$10,MATCH(B$12,$A$1:$F$1,0),0)
=LOOKUP(1,0/($A$2:$A$10=$A13),OFFSET($A$2:$A$10,,MATCH(B$12,$A$1:$F$1,)-1))
5、根据*逆序查找俗称
=VLOOKUP(A13,IF({1,0},$B$2:$B$10,$A$2:$A$10),2,0)
=LOOKUP(1,0/($B$2:$B$10=A13),$A$2:$A$10)
6、根据俗称跟订单号两个条件查询完成情况
=VLOOKUP(A12&B12,IF({1,0},$A$2:$A$9&$C$2:$C$9,$E$2:$E$9),2,0)
=LOOKUP(1,0/($A$2:$A$9&$C$2:$C$9=A12&B12),$E$2:$E$9)
7、根据不完整的俗称查询*
=VLOOKUP(D2&"*",A:B,2,0)
=LOOKUP(1,0/(LEFT($A$2:$A$5)=D2),$B$2:$B$5)
8、根据成绩的区间判断等级
=VLOOKUP(E2,A:C,3)
=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)
如果你觉得不错,请分享给你的朋友们,独乐乐不如众乐乐