只會Vlookup函數Out了！Excel所有查找公式全在這兒

來源: Excel培訓精英編輯：蘇米亞 2019/08/26 11:29:11　　字體：大小

選課中心

實務會員買一送一

選課中心

資料專區(qū)

需要的都在這里

資料專區(qū)

課程試聽

搶先體驗

課程試聽

高薪就業(yè)

從零基礎到經理

高薪就業(yè)

找對比，你會首先想到Vlookup函數。但在Excel中只會Vlookup函數是遠遠不夠的。今天我們就來對查找公式進行一次全面的整理（注：一題多解的只選取更優(yōu)公式），小伙伴們，趕快一起來看看吧。

正保會計網校

1、普通查找

查找李曉峰的應發(fā)工資

=VLOOKUP(H2,B:F,5,0)

正保會計網校

2、反向查找

查找吳剛的員工編號

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

正保會計網校

3、交叉查找

查找3月辦公費的金額

=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)

正保會計網校

4、多條件查找

查找上海產品B的銷量

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

正保會計網校

5、區(qū)間查找

根據銷量從右表中查找提成比率。

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

正保會計網校

6、雙區(qū)間查找

根據銷量和比率完成情況，從表中查找返利。

=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))

正保會計網校

7、線型插值

如下圖所示，A列是數量，B列是數量對應的系數值?，F要求出數字8所對應的系數值。

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

正保會計網校

8、查找最后一個符合條件記錄

如下圖所示，要求查找A產品的最后一次進價。

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

正保會計網校

9、模糊查找

如下圖所示，要求根據提供的城市從上表中查找該市名的第2列的值。

=VLOOKUP("*"&A7&"*",A1:B4,2,0)

正保會計網校

10、匹配查找

如下圖所示，要求根據地址從上表中查找所在城市的提成。

=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

正保會計網校

11、最后一個非空值查找

如下圖所示，要求查找最后一次還款日期

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

正保會計網校

12、多工作表查找

【例10】從各部門中查找員工的基本工資，在哪一個表中不一定。

方法1：

=IFERROR(VLOOKUP(A2,服務!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無此人信息")))))

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售"1473"服務"1473"人事"1473"綜合"1473"財務"}&"!a:a"),A2),{"銷售"1473"服務"1473"人事"1473"綜合"1473"財務"})&"!a:g"),7,0)

正保會計網校

13、一對多查找

【例】根據產品查找相對應的所有供應商

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

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

正保會計網校

14、查找銷量最大的城市

查找銷量最大的城市（數組公式按ctrl+shift+enter三鍵輸入）

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

正保會計網校

15、最接近值查找

根據D4的價格，在B列查找最接近的價格，并返回相對應的日期

（數組公式按ctrl+shift+enter三鍵輸入）

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

正保會計網校

16、跨多文件查找

跨多個文件查找，估計你搜遍網絡也找不到，這也是蘭色首次編寫跨多文件查找公式。其實原理和跨多表查找一樣，也是借助lookup等函數實現。

文件夾中有N個倉庫產品表格，需要在“查詢”文件完成查詢

正保會計網校

倉庫表樣式

正保會計網校

在查詢表中設置公式，根據產品名稱從指定的文件中sheet1工作表查詢入庫單價

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"倉庫1"1473"倉庫2"1473"倉庫3"}&".xlsx]sheet1!a:a"),A2),"["&{"倉庫1"1473"倉庫2"1473"倉庫3"}&".xlsx]sheet1")&"!a:b"),2,0)。

正保會計網校