VLOOKUP函數(shù)查找技巧

VLOOKUP函數(shù)查找技巧

這篇文章中，我們將探討VLOOKUP函數(shù)的第1個(gè)參數(shù)，介紹一些查找方法和技巧。

情形1：查找數(shù)值的數(shù)據(jù)類(lèi)型不一致

相同的值但以不同的數(shù)據(jù)類(lèi)型來(lái)存儲(chǔ)，對(duì)于VLOOKUP函數(shù)來(lái)說(shuō)是不同的。

在單元格中，可以存儲(chǔ)不同類(lèi)型的數(shù)據(jù)，例如數(shù)字、文本字符串、日期和布爾值。在單元格中輸入4000時(shí)，Excel通常將其識(shí)別并存儲(chǔ)為數(shù)字。Excel默認(rèn)右對(duì)齊數(shù)字。

有時(shí)，當(dāng)從其他數(shù)據(jù)源導(dǎo)入數(shù)據(jù)到Excel中時(shí)，Excel會(huì)對(duì)數(shù)據(jù)類(lèi)型進(jìn)行假設(shè)，會(huì)將數(shù)字存儲(chǔ)為文本字符串。Excel默認(rèn)左對(duì)齊文本字符串。

圖1

此時(shí)，如果使用VLOOKUP函數(shù)來(lái)匹配這兩個(gè)值（一個(gè)值存儲(chǔ)為數(shù)字，一個(gè)值存儲(chǔ)為文本字符串），則不會(huì)匹配。當(dāng)作為不同的數(shù)據(jù)類(lèi)型存儲(chǔ)時(shí)，VLOOKUP將不匹配等效值。如下圖2所示，嘗試查找編號(hào)對(duì)應(yīng)的物品名稱(chēng)時(shí)，會(huì)返回錯(cuò)誤。

圖2

技巧：使用TEXT函數(shù)作為VLOOKUP函數(shù)的第1個(gè)參數(shù)

TEXT函數(shù)將數(shù)字轉(zhuǎn)換為文本字符串。通過(guò)在VLOOKUP函數(shù)的第1個(gè)參數(shù)中使用TEXT函數(shù)，使查找值的類(lèi)型匹配。

TEXT函數(shù)有兩個(gè)參數(shù)，第1個(gè)參數(shù)是要轉(zhuǎn)換的值，第2個(gè)參數(shù)是格式代碼。因?yàn)槲覀儾魂P(guān)心格式代碼，所以對(duì)第2個(gè)參數(shù)使用0。

在圖2中，查找編號(hào)對(duì)應(yīng)的物品名稱(chēng)的公式修改為：

=VLOOKUP(TEXT(A11,0),表1,2,0)

顯示正確的查找結(jié)果，如圖3所示。

圖3

當(dāng)然，如果想要將數(shù)值文本轉(zhuǎn)換成數(shù)值，可以使用VALUE函數(shù)。

更進(jìn)一步，如果想要公式既滿足數(shù)值文本，又適合數(shù)值，可以使用IFERROR函數(shù)：

=IFEEROR(VLOOKUP(TEXT(A11,0),表1,2,0),VLOOKUP(VALUE(A11,0),表1,2,0))

情形2：查找值在不同的列

有時(shí)，查找值不在同一列，如何使用同一公式來(lái)實(shí)現(xiàn)查找。

圖4中灰色背景的單元格是要根據(jù)其左側(cè)單元格值來(lái)獲取相應(yīng)的數(shù)據(jù)。

圖4

在圖5所示的表2中存儲(chǔ)著原數(shù)據(jù)。

圖5

使用VLOOKUP函數(shù)從表2中獲取數(shù)據(jù)。在單元格D9中的公式：

=VLOOKUP(A9,表2,2,0)

結(jié)果如圖6所示。

圖6

然后，我們將公式復(fù)制到其他單元格中，如圖7所示。可以看出，在單元格D14和D15中發(fā)生錯(cuò)誤。

圖7

很顯然，出現(xiàn)錯(cuò)誤的原因在于復(fù)制公式后，公式會(huì)自然地改變?yōu)椴檎乙脝卧駷锳14和A15，如圖8所示。而實(shí)際上要查找的單元格為B14和B15，即這里的查找值與原公式查找值在不同的列。

圖8

一個(gè)簡(jiǎn)單的方法是，將公式中的A14修改為B14。然而，如果有許多這樣的公式，修改起來(lái)很麻煩。能否使用同一個(gè)公式而無(wú)須修改呢？這樣，公式更容易更新和維護(hù)。

技巧：在VLOOKUP函數(shù)的第1個(gè)參數(shù)中使用連接運(yùn)算

通過(guò)連接值來(lái)創(chuàng)建單個(gè)文本字符串，其中一種方法是使用連接運(yùn)算符&。修改上圖6中的公式為：

=VLOOKUP(A9&B9,表2,2,0)

將公式復(fù)制到其他單元格中，結(jié)果如圖9所示。

圖9

情形3：查找值包含空格時(shí)

如果要查找的文本字符串包含前導(dǎo)空格、中間空格或尾空格，而在查找表中沒(méi)有空格，那么VLOOKUP函數(shù)就會(huì)返回錯(cuò)誤結(jié)果。

如圖10所示，根據(jù)產(chǎn)品編號(hào)在表4中查找相應(yīng)的成本。

圖10

表4如圖11所示。

圖11

在圖10中，單元格C10中的公式為：

=VLOOKUP(A10,表4,2,0)

結(jié)果返回錯(cuò)誤值，如圖12所示。

圖12

為什么會(huì)這樣？仔細(xì)檢查，發(fā)現(xiàn)在單元格A10中的數(shù)據(jù)結(jié)尾包含有空格。

技巧：在VLOOKUP函數(shù)的第1個(gè)參數(shù)中使用TRIM函數(shù)

可以使用TRIM函數(shù)移除文本字符串中多余的空格。因此，將單元格C10中的公式修改為：

=VLOOKUP(TRIM(A10),表4,2,0)

將公式下拉至單元格C14，結(jié)果如圖13所示。

圖13

情形4：部分匹配

有時(shí)，查找的值只是查找表中數(shù)據(jù)的部分內(nèi)容，查找表如下圖14所示的表5。

圖14

單元格A9中是查找值，要在單元格B5中返回查找的結(jié)果。使用公式：

=VLOOKUP(A9,表5,2,FALSE)

獲得的結(jié)果為#N/A，如圖15所示，

圖15

當(dāng)然，你可以使用我們前面介紹的技巧，將表5中的數(shù)據(jù)排序后再進(jìn)行近似匹配，可能會(huì)返回所需要的結(jié)果。然而，我們這里使用更合理的部分匹配技巧。

技巧：在VLOOKUP函數(shù)的第1個(gè)參數(shù)中使用通配符

通配符是可以代表其他字符的一個(gè)字符。例如，星號(hào)（*）可以代表任意數(shù)量的字符。因此，我們需要將查找值與星號(hào)相連接。修改后的公式如下：

=VLOOKUP(A9&”*”,表5,2,FALSE)

結(jié)果如圖16所示。

圖16

在表中的數(shù)據(jù)后面包含查找值時(shí)，可以使用”*”&A9查找。在表中的數(shù)據(jù)中間包含查找值時(shí)，可以使用”*”&A9”*”。

結(jié)語(yǔ)

在使用VLOOKUP函數(shù)時(shí)，結(jié)合具體情形，將其第1個(gè)參數(shù)進(jìn)行適當(dāng)?shù)恼{(diào)整，就能夠達(dá)到返回正確的數(shù)據(jù)的目的。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。