前言:經常有同學提問,我是一個初學者,應該先學習哪些函數公式。蘭色就把工作中經常用到的查找公式進行總結,共14個類別31個公式,希望能對新手有所幫助。
Excel表格查找公式大全
1、普通查找
在A列查找D1的值,返回B列的值
=Vlookup(d1,a:b,2,0)
=index(b:b,match(d1,a:a,0))
=xlookup(d1,a:a,b:b)
2、從右至左查找
在B列查找D1的值,返回A列的值
=Vlookup(d1,if({1,0},b:b,a:a),2,0)
=index(A:A,match(d1,B:B,0))
=xlookup(d1,B:B,A:A)
3、包含查找
查找A列含蘋果,返回B列的值
=Vlookup("*蘋果*",A:B,2,0)
=Xlookup("*蘋果*",A:A,B:B,,2)
4、多條件查找
根據D1和E1的值,在A和B列查找,返回C列值
=lookup(1,0/((a1:a100=d1)*(b1:b100)=e1),c1:c100)
=xlookup(d1&e1,a1:a100&b1:b100,c1:c100)
=index(c1:c100,match(d1&,e1,a1:a100&b1:b100,0))
5、格式不一致查找
用文本查找數字列
=Vlookup(d1*1,A:B,2,0)
用數字查找文本型數字列
=Vlookup(d1&"",A:B,2,0)
6、重複值查找
查找返回多個結果用並逗號連接在一起
=TEXTJOIN(",",1,IF(B2:B8=A12,C2:C8,""))
=TEXTJOIN(",",1,filter(C2:C8,b2:b8=a12))
7、篩選式查找
篩選B列爲財務部的所有行
=filter(A1:D100,B1:B100="財務部")
篩選B列爲財務部的所有行(結果不含B列部門)
=filter(Choose(A1:D100,1,3,4),B1:B100="財務部")
8、從多個表格查找
在多個部門表中查找員工
=IFNA(VLOOKUP(A2,財務部!A:B,2,0),IFNA(VLOOKUP(A2,人事部!A:B,2,0),IFNA(VLOOKUP(A2,客服部!A:B,2,0),"")))
配合VSTACK公式
=Vlookup(A1,VSATCK(財務部:客服部!a1:b100),2,0)
9、數據區間查找
C列是升序排列的數字,D列是返回的值
=Lookup(A1,C:D)
=Vlookup(A1,C:D,2)
10、忽略隱藏行的查找
查找時跳過隱藏行
=VLOOKUP(E19,IF(SUBTOTAL(103,、OFFSET(A1,ROW(1:13),0)),B2:C14),2,0)
11、帶鏈接的查找
在A列查找返回C列的值,並添加指向A列的鏈接
=HYPERLINK("#A"&MATCH(F2,A:A,0),
VLOOKUP(F2,A:D,3,0))
12、從後向前查找
C列是升序排列的數字,D列是返回的值
=Lookup(1,0/(a1:a100="A産品"),b1:b100)
=Xlookup("A産品",a:a100,b1:b100,,,-1)
13、最後一個查找
A列最後一個文字
=LOOKUP("座",A:A)
A列最後一個數字
=LOOKUP(9^999,A:A)
14、含特殊符號的查找
要查找的A列字符中含有*號
=VLOOKUP(SUBSTITUTE(D2,"*","~*"),A:B,2,0)
用Tocol函數
=TOCOL(IF(A2:A7=D2,B2:B7,x),2)