数据查找excel函数集合 好不容易整理完，希望有你能用的！

1、正向查找

=VLOOKUP(F2,B2:D13,2,0)

2、反向查找

如上图

=INDEX(A:A,MATCH(F2,B:B,0))

3交叉查找

=VLOOKUP(F2,A:D,MATCH(G2,1:1,0),0)

4、多条件查询

=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

5、区间查找

=LOOKUP(A2,$D$2:$E$5)

双区间查找

=INDEX(B2:F6,MATCH(D11,A2:A6),MATCH(E11,B1:F1))

6线型插值

=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)

7查找最后一个符合条件记录

=LOOKUP(1,0/(B2:B9=A13),C2:C9)

8模糊查找

=VLOOKUP("*"&A9&"*",A1:B5,2,0)

匹配查找

=LOOKUP(9^9,FIND(A$3:A$6,A10),B$3:B$6)

9最后一个非空值查找

=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)

10多工作表查找

=IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))

=VLOOKUP(A3,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"人事";"财务"}&"!a:a"),A3),{"销售";"人事";"财务"})&"!a:g"),7,0)

11一对多查找

A2 =B2&COUNTIF(B$1:B2,B2)

B13 =IFERROR(VLOOKUP($A13&COLUMN(A1),$A:$C,3,0),"")

12查找最大的

=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))

14差最近的日期

=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)