找對(duì)比，你會(huì)首先想到

Vlookup

函數(shù)。但在Excel中只會(huì)Vlookup函數(shù)是遠(yuǎn)遠(yuǎn)不夠的。今天蘭色對(duì)查找公式進(jìn)行一次全面的整理。

對(duì)于一個(gè)地方需要用到對(duì)比的時(shí)候我們肯定想到的是Vlookup函數(shù)，但是你在用Excel的時(shí)候只會(huì)用它可不行，今天我們就一起來(lái)梳理下常用的！

地球人都知道：一題多解的只選取最優(yōu)公式！

1、普通查找VLOOKUP函數(shù)

我們需要查找李曉峰的應(yīng)發(fā)工資

公式：=VLOOKUP(H2,B:F,5,0)

2、反向查找INDEX

函數(shù)

查找吳剛的員工編號(hào)

公式：=INDEX(A:A,MATCH(H2,B:B,0))

反向查找

3、交叉查找VLOOKUP函數(shù)：

查找3月辦公費(fèi)的金額

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

4、多條件查找

VLOOKUP函數(shù)

查找上海產(chǎn)品B的銷量

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

5、區(qū)間查找

根據(jù)銷量從右表中查找提成比率。

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

6、雙區(qū)間查找

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

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

7、線型插值

A列是數(shù)量，B列是數(shù)量對(duì)應(yīng)的系數(shù)值?，F(xiàn)要求出數(shù)字8所對(duì)應(yīng)的系數(shù)值。

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

8、查找最后一個(gè)符合條件記錄

要求查找A產(chǎn)品的最后一次進(jìn)價(jià)。

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

9、模糊查找

要求根據(jù)提供的城市從上表中查找該市名的第2列的值。

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

10、匹配查找

要求根據(jù)地址從上表中查找所在城市的提成。

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

11、最后一個(gè)非空值查找

要求查找最后一次還款日期

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

12、多工作表查找

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

方法1

公式：=IFERROR(VLOOKUP(A2,服務(wù)!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財(cái)務(wù)!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無(wú)此人信息")))))

方法2：

公式：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務(wù)";"人事";"綜合";"財(cái)務(wù)"}&"!a:a"),A2),{"銷售";"服務(wù)";"人事";"綜合";"財(cái)務(wù)"})&"!a:g"),7,0)

13、一對(duì)多查找

【例】根據(jù)產(chǎn)品查找相對(duì)應(yīng)的所有供應(yīng)商

公式：

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

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

14、查找銷量最大的城市

查找銷量最大的城市。注意：注意：注意：數(shù)組公式按ctrl+shift+enter三鍵輸入

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

15、最接近值查找

根據(jù)D4的價(jià)格，在B列查找最接近的價(jià)格，并返回相對(duì)應(yīng)的日期

注意：注意：注意：數(shù)組公式按ctrl+shift+enter三鍵輸入

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

15、跨多文件查找

跨多個(gè)文件查找，網(wǎng)上大概很難找到這樣的教程，仔細(xì)想想其實(shí)原理和跨多表查找一樣，也是借助lookup等函數(shù)實(shí)現(xiàn)。

文件夾中有N個(gè)倉(cāng)庫(kù)產(chǎn)品表格，需要在“查詢”文件完成查詢

倉(cāng)庫(kù)表樣式

在查詢表中設(shè)置公式，根據(jù)產(chǎn)品名稱從指定的文件中sheet1工作表查詢

入庫(kù)單價(jià)

公式：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"倉(cāng)庫(kù)1";"倉(cāng)庫(kù)2";"倉(cāng)庫(kù)3"}&".xlsx]sheet1!a:a"),A2),"["&{"倉(cāng)庫(kù)1";"倉(cāng)庫(kù)2";"倉(cāng)庫(kù)3"}&".xlsx]sheet1")&"!a:b"),2,0)。

如果你在用vlookup函數(shù)做多文件查找的時(shí)候也可以用iferror+vlookup的模式的這種模式，看起來(lái)公式很長(zhǎng)，但是這樣不會(huì)出錯(cuò)。此外，如果一個(gè)表用到的函數(shù)太多就需要用宏表函數(shù)用Files獲取所有excel文件名稱了。

老男孩PS

：我能想到的基本上都已經(jīng)列出來(lái)了，但是我相信有很多大家可能看不懂，但是看不懂沒(méi)關(guān)系，趕緊收藏起來(lái)，然后直接套用就可以了。

　　1、如圖：可以清楚看到，做的這個(gè)簡(jiǎn)單表格中包含“25”這樣一個(gè)數(shù)值。

　　2、下面用“查找”的方法，查找一下“25”，ctrl+F打開“查找”對(duì)話框，在查找內(nèi)容里面的輸入“25”。

　　3、點(diǎn)擊確定按鈕，Excel卻彈出一個(gè)查找不到的內(nèi)容的提示。到底是什么原因呢。明明看到了有25這個(gè)數(shù)值??？

　　4、其實(shí)大家是對(duì)“查找替換”功能掌握不夠?qū)е碌?。點(diǎn)擊“查找替換”中的“選項(xiàng)”。

　　5、在“查找范圍”里，大家可以將查找范圍改成“值”。

　　6、下面再查找一下，是不是發(fā)現(xiàn)25個(gè)這個(gè)單元格是可以找到了。

　　7、下面分析一下原因。其實(shí)可以發(fā)現(xiàn)單元格內(nèi)容“25”其實(shí)是一個(gè)公式計(jì)算結(jié)果。Excel默認(rèn)的“查找范圍”是“公式”，也就是查找公式表達(dá)式里的字符，但不查找公式計(jì)算得到的結(jié)果，所以就出現(xiàn)了明明存在的內(nèi)容為什么查找不到的現(xiàn)象。

（本文內(nèi)容由百度知道網(wǎng)友yjtkgg321貢獻(xiàn)）

在使用excel表格的時(shí)候，想要找出一些相同條件下的數(shù)據(jù)會(huì)很費(fèi)勁，發(fā)現(xiàn)有很多數(shù)據(jù)寫錯(cuò)了，要一個(gè)個(gè)進(jìn)行修改的話就更麻煩，不過(guò)會(huì)使用excel表格中的查找和替換功能就比較方便，接下來(lái)以excel表格2016版為例，跟大家分享一下excel表格如何使用查找和替換功能。

以下面考試系統(tǒng)為例：表格中好多信息，我們現(xiàn)在需要在眾多信息中找到李明同學(xué)的信息，如下面動(dòng)畫所示，打開開始菜單，點(diǎn)擊在“編輯”中“查找和選擇”，下拉菜單中點(diǎn)擊“查找”項(xiàng)目，彈出“查找和替換”對(duì)話框，查找內(nèi)容輸入“李明”，確定后完成李明信息的查找。

我們要把WORD替換為EXCEL，一個(gè)一個(gè)的替換肯定比較麻煩，通過(guò)替換功能可以快速解決。打開開始菜單，點(diǎn)擊在“編輯”中“查找和選擇”，下拉菜單中點(diǎn)擊“替換”項(xiàng)目，彈出“查找和替換”對(duì)話框，查找內(nèi)容輸入“WORD”，替換內(nèi)容輸入“EXCEL”,確定后完成考試科目的替換。

本文所有圖片、文字均為原創(chuàng)，以上教程截圖來(lái)自EXCEL2016，其他版本的EXCEL設(shè)置方法類似，如果你感覺(jué)本文對(duì)你有幫助，記著關(guān)注“光明教你做表格”哦，每天一個(gè)小技能，讓你快速成為高手??！

之前有位學(xué)員來(lái)上課的時(shí)候，小編聽到她在抱怨說(shuō)看了半天index函數(shù)和match函數(shù)，但是不知道該怎么樣去使用它們？確實(shí)這兩個(gè)函數(shù)看著挺簡(jiǎn)單，分開使用也還好，但是想要發(fā)揮最大的效果就必須要交叉使用才可以。

精確定位：MATCH函數(shù)

MATCH函數(shù)(lookup-value,lookup-array,match-type)，返回查找內(nèi)容所在的位置。

lookup-value:表示需要查找的值，數(shù)組或單元格引用都可以；

lookup-array:表示包含所要查找數(shù)值的區(qū)域，數(shù)組或數(shù)組引用；

match-type:表示用于精確查找或模糊查找。取值為-1、1、0 。其中0為精確查找。

檢索查詢和交叉查詢：index函數(shù)

INDEX函數(shù)(array,row-num,column-num)，返回制定位置中的內(nèi)容。

array:要查找值的單元格區(qū)域或數(shù)組；

row-num:返回值所在的行號(hào)；

column-num:返回值所在的列號(hào)（行號(hào)和列號(hào)有一個(gè)即可）。

實(shí)訓(xùn)習(xí)題：

左邊是公司所有人員的信息統(tǒng)計(jì)表，右邊是我們需要查找的信息。我們想用名字去查找這個(gè)人所在的部門。

我們需要將名字所在的位置使用match定位出來(lái)，再和index函數(shù)配合使用。選擇姓名所在位置，再選擇姓名所在單元格范圍，使用精確定位即可。

在使用了match后，在使用index函數(shù)會(huì)出現(xiàn)這樣的對(duì)話框，選擇第一個(gè)點(diǎn)擊確定即可。配合index函數(shù)，選擇部門所在單元格范圍即可完成操作。

當(dāng)然在使用函數(shù)的過(guò)程中，小編也犯了一個(gè)錯(cuò)誤，就是沒(méi)有使用絕對(duì)應(yīng)用，到時(shí)候出現(xiàn)的結(jié)果會(huì)不正確，所以我們需要在要改變的數(shù)據(jù)前面加上$鎖死數(shù)據(jù)。

在excel軟件中，查找和替換功能也是我們比較常用的功能，它可以幫我們快速定位到工作表我們所要查找的數(shù)據(jù)或文字內(nèi)容，并且可以對(duì)其進(jìn)行修改，絕對(duì)是查資料的一大神器。

在這里，因?yàn)橄挛牡男枰?，我先?duì)查找和替換的操作進(jìn)行簡(jiǎn)單的概括：在office2016中，首先我們找到并點(diǎn)擊【開始】選項(xiàng)卡，然后在功能欄最右邊找到并點(diǎn)擊【查找與選擇】，在彈出的下拉列選項(xiàng)中，我們點(diǎn)擊【查找】或【替換】，就會(huì)彈出查找和替換的對(duì)話框，這樣我們就能進(jìn)行查找或替換的操作了。具體操作如下圖所示：

簡(jiǎn)單的查找和替換問(wèn)題自然沒(méi)有任何問(wèn)題，我們?cè)诓檎覂?nèi)容和替換為上填入具體的內(nèi)容，然后就能萬(wàn)事大吉！但我這里有這樣一個(gè)案例。

某地某班級(jí)對(duì)班里學(xué)生進(jìn)行了出生地統(tǒng)計(jì)，統(tǒng)計(jì)好了卻出了一個(gè)小問(wèn)題，該班共有十個(gè)人來(lái)自“小港村”，因?yàn)榱?xí)慣問(wèn)題，有六個(gè)人只填了“小港”二字，最后也一起匯總填到了excel表上，但是學(xué)校有要求，出生地填寫要清楚明白，所以現(xiàn)在的任務(wù)就成了把“小港”改為“小港村”。

大家肯定想到了，要用到excel里的查找與替換功能。ok，我們按照上面步驟先調(diào)出查找和替換的對(duì)話框，然后在查找內(nèi)容后面填入：小港，在替換為后面填入：小港村，然后點(diǎn)擊全部替換，ok？具體操作如下圖所示：

試試證明真的不行，我們會(huì)發(fā)現(xiàn)我們給所有的“小港”和“小港村”做了次加法，當(dāng)然有好消息“小港”變成了“小港村”，但壞消息是“小港村”變成了“小港村村”，這是為什么呢？想想也很容易，我們?cè)诓檎覂?nèi)容后面填入：小港，那就意味著excel會(huì)幫你找到所有的“小港”，然后進(jìn)行替換操作，這也就是“小港村”變成了“小港村村”的原因了。那么我們?cè)趺匆?guī)避這樣的錯(cuò)誤呢？正確的做法如下：

首先還是和上文一樣，調(diào)出查找和替換的對(duì)話框，然后在查找內(nèi)容后面填入：小港，在替換為后面填入：小港村。然后別急著操作，先點(diǎn)擊查找和替換的對(duì)話框中的【選項(xiàng)】按鈕，勾選【單元格匹配】，然后單擊全部替換，這樣“小港”變成了“小港村”，原本的“小港村”保持不變。這樣我們的錯(cuò)誤就完美解決了。具體操作如下圖所示：

（PS：勾選【單元格匹配】后，excel工作表會(huì)以單元格為單位進(jìn)行，而不是以文字為單位進(jìn)行比對(duì)，這樣就成功規(guī)避上面的問(wèn)題）

覺(jué)得對(duì)你們有用的小伙伴們請(qǐng)點(diǎn)贊關(guān)注吧！您的鼓勵(lì)是我前進(jìn)的動(dòng)力，也希望擅長(zhǎng)運(yùn)用辦公軟件的小伙伴們能夠不吝賜教，積極的留言，教會(huì)小編更多的excel運(yùn)用的小技巧，歡迎一起來(lái)探討學(xué)習(xí)?。?！

模糊及精準(zhǔn)查詢

如圖：Excel文件中包含多個(gè)數(shù)值，它們都有一個(gè)特點(diǎn)就是都含有6這樣一個(gè)數(shù)值。

CTRL+F打開查找對(duì)話框，我在查找內(nèi)容是“6”但大家可以清楚看到，只要單元格含6都可以查找出來(lái)。這其實(shí)與我們本來(lái)是查找“6”的真實(shí)目的不一致。因?yàn)镋xcel中默認(rèn)的查找方式是模糊查找

怎么樣才能夠達(dá)到精確查找呢？在查找對(duì)話框中點(diǎn)擊“選項(xiàng)”

在“選項(xiàng)”中點(diǎn)擊勾選“單元格匹配”單元格匹配什么意思？就是查找內(nèi)容與單元格完全一致時(shí)還是才查找。再看一下查找結(jié)果，是不是達(dá)到了我們要的精確查找的結(jié)果？

vlookup查找

如圖所示，要根據(jù)I2單元格活動(dòng)形式，在E-G區(qū)域中查詢出對(duì)應(yīng)的價(jià)格

經(jīng)典套路：

=VLOOKUP(I2,E:G,3,0)

lookup查詢

如下圖所示，根據(jù)H2單元格姓名，在A-B數(shù)據(jù)區(qū)域中查詢對(duì)應(yīng)的工號(hào)

=LOOKUP(1,0/(H2=B2:B31),A2:A31)

公式指南：

=LOOKUP(1,0/(條件區(qū)域=指定條件),要返回的區(qū)域)

組合查找

利用MATCH和INDEX函數(shù)

如下圖所示，根據(jù)姓名查詢城市及職務(wù)。

G2單元格公式為：

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

H2單元格公式為：

=INDEX(C:C,MATCH($F2,$B:$B,0),0)

如果看了這篇文章還是搞不定，可以直接給我留言

關(guān)于作者

專注分享Excel經(jīng)驗(yàn)技巧

一個(gè)人走得快，一群人走得遠(yuǎn)，與195位同學(xué)共同成長(zhǎng)

1.如圖：Excel文件中包含多個(gè)數(shù)值，它們都有一個(gè)特點(diǎn)就是都含有5這樣一個(gè)數(shù)值。

2.CTRL+F打開查找對(duì)話框，在查找內(nèi)容是“5”但可以清楚看到，只要單元格含5都可以查找出來(lái)。這其實(shí)與我們本來(lái)是查找“5”的真實(shí)目的不一致。因?yàn)镋xcel中默認(rèn)的查找方式是模糊查找。

3.在查找對(duì)話框中點(diǎn)擊“選項(xiàng)”。

4.在“選項(xiàng)”中點(diǎn)擊勾選“單元格匹配”，就是查找內(nèi)容與單元格完全一致時(shí)還是才查找。再看一下查找結(jié)果。

（本文內(nèi)容由百度知道網(wǎng)友茗童貢獻(xiàn)）

Excel中合并單元格，小編一直覺(jué)得是制表的大忌，讓我們后續(xù)數(shù)據(jù)處理增加不少難度。但仍然還是有伙伴因?yàn)橐恍┰蛴昧撕喜卧?。這位伙伴就是遇到這樣的麻煩。詢問(wèn)Excel中合并單元格查詢公式問(wèn)題。

需要根據(jù)E2單元格的銷售員，查詢銷售出去的訂單對(duì)應(yīng)的產(chǎn)品代碼。

F2單元格公式為：=LOOKUP("座",INDIRECT("a1:a"&MATCH(E2,$C$1:$C$9,)))

公式有點(diǎn)小小的難度。一起來(lái)解讀一下：

MATCH(E2,$C$1:$C$9,)，查找E2在C列中是位置，返回5。

INDIRECT("a1:a"&MATCH(E2,$C$1:$C$9,))：返回引用單元格區(qū)域，得到值為：INDIRECT("a1:a"&5)，即：a1：a5單元格區(qū)域。

LOOKUP("座",引用區(qū)域)代表：返回引用區(qū)域中的最后一個(gè)文本。在E2公式中是返回a1：a5單元格區(qū)域的最后一個(gè)文本，也就是A446580684。

在查找Excel表格的內(nèi)容時(shí)，有時(shí)可能會(huì)想要在查找到的內(nèi)容中進(jìn)行查找。例如在下圖表格中查找出包含“廣東省”的單元格后，又想在查找到的內(nèi)容中查找包含“A分公司”的單元格。

●第一步查找包含“廣東省”的單元格比較容易，鼠標(biāo)點(diǎn)擊Excel開始選項(xiàng)卡中的“查找”或者按鍵盤的“Ctrl+F”鍵后，在查找內(nèi)容處輸入“廣東省”，再點(diǎn)擊“查找全部”按鈕。

●如果找到的內(nèi)容較多，不能全部顯示出來(lái)，可以按住鼠標(biāo)左鍵拖動(dòng)查找界面下方的邊緣處，擴(kuò)大顯示界面。

●如果想在查找到的內(nèi)容中再查找包含“A分公司”的內(nèi)容，可以先用鼠標(biāo)左鍵點(diǎn)擊查找到的任意一條結(jié)果，點(diǎn)擊后該結(jié)果會(huì)呈現(xiàn)深色背景，表示該結(jié)果已經(jīng)被選中。（如果點(diǎn)擊“查找全部”按鈕后未進(jìn)行其他操作，最上方的那一條查找結(jié)果默認(rèn)會(huì)自動(dòng)被選中，這時(shí)則不必再點(diǎn)擊鼠標(biāo)。）

●此時(shí)按鍵盤的“Ctrl+A”鍵，則查找結(jié)果中的所有內(nèi)容都會(huì)被選中。

●如果這時(shí)查看工作表，會(huì)看見所有查找到的單元格都變成了深色背景，即都處于被選中的狀態(tài)，

●這時(shí)在查找內(nèi)容處輸入“A分公司”，再點(diǎn)擊“查找全部”按鈕。

●點(diǎn)擊“查找全部”按鈕后，查找結(jié)果列表中就會(huì)顯示出再次查找后的內(nèi)容了，這個(gè)查找結(jié)果不是查找整個(gè)工作表的結(jié)果，而是在上一次查找結(jié)果中進(jìn)行查找的結(jié)果。

用這種方法可以循環(huán)多次的在查找到的內(nèi)容中進(jìn)行查找。

PS

本文的例表內(nèi)容較少，只是為了舉例說(shuō)明如何進(jìn)行二次查找。像本文這樣內(nèi)容不多的表格也可以使用通配符“*”配合查找，本例中在查找內(nèi)容處輸入“廣東省*A分公司”也可以達(dá)到同樣的目的。

使用Excel時(shí)，經(jīng)常需要在表格中查找指定的數(shù)據(jù)或者文字等內(nèi)容。當(dāng)然，查找的操作比較簡(jiǎn)單，但Excel中默認(rèn)只是在當(dāng)前的工作表（Sheet）中進(jìn)行查找。如果一個(gè)Excel工作簿（或者說(shuō)是一個(gè)Excel文檔）中有很多工作表，那么要想在所有的工作表中查找指定內(nèi)容，默認(rèn)設(shè)置下就需要逐個(gè)打開各個(gè)工作表，再逐個(gè)進(jìn)行查找操作。這種情況時(shí)我們可以在查找時(shí)特別設(shè)置一下，讓Excel在當(dāng)前工作簿的所有工作表中進(jìn)行查找，從而提高工作效率。下面以Excel2007為例介紹如何設(shè)置，以供參考。

●點(diǎn)擊Excel中的“查找”或者按鍵盤的“Ctrl+F”組合鍵打開“查找和替換”對(duì)話框后，點(diǎn)擊其中的“選項(xiàng)”按鈕。

●點(diǎn)擊選項(xiàng)按鈕后，查找和選擇對(duì)話框中會(huì)展開更多的選項(xiàng)。如果在“范圍”選項(xiàng)右側(cè)顯示的是“工作表”，則說(shuō)明現(xiàn)在設(shè)置的只是在當(dāng)前的工作表中進(jìn)行查找。如果想要查找所有的工作表，需點(diǎn)擊“范圍”右側(cè)的下拉框。

●彈出下拉列表后，在列表中點(diǎn)擊“工作簿”選項(xiàng)。即將查找范圍設(shè)置成了當(dāng)前工作簿中的所有工作表。

●然后再輸入要查找的內(nèi)容，點(diǎn)擊“查找全部”按鈕。如果想逐個(gè)顯示查找結(jié)果，則需點(diǎn)擊“查找下一個(gè)”按鈕。

●點(diǎn)擊“查找全部”按鈕后，對(duì)話框下方會(huì)顯示出查找結(jié)果列表，在其中的“工作表”列中會(huì)顯示包含查找內(nèi)容的工作表名稱。在某個(gè)查找結(jié)果所在的行中點(diǎn)擊鼠標(biāo)，Excel就會(huì)自動(dòng)打開對(duì)應(yīng)的工作表，并且選中對(duì)應(yīng)的單元格。

這個(gè)小技巧可以讓我們?cè)贓xcel工作簿中進(jìn)行查找時(shí)更快速的找到需要的結(jié)果，提高工作效率。