Excel 中最常用的功能之一是 VLOOKUP 函數(shù)。它允許用戶在表中查找數(shù)據(jù)并返回相應(yīng)的值。然而，雖然 VLOOKUP 是一個強(qiáng)大的工具，但它也有其局限性。在本文中，我將探討 VLOOKUP 的 8 個限制，并提供一些最佳解決方案來幫助您克服這些限制并充分利用 Excel 電子表格。

第一個限制：VLOOKUP 只能向右搜索，不能向左搜索

VLOOKUP 函數(shù)的主要缺點之一是它只能在表數(shù)組中向右查找。如果要提取的數(shù)據(jù)位于查找值的左側(cè)，則此函數(shù)將返回 #N/A 錯誤。

在下面的場景中，我嘗試查找員工 ID 105 的工資。

公式

=VLOOKUP(A2,A5:D12,4,FALSE)

VLOOKUP 函數(shù)完美運行并返回了相應(yīng)的工資，,000。在本例中，我想要提取的數(shù)據(jù)（薪水）位于查找值（ID）的右側(cè)。

現(xiàn)在，我們嘗試使用VLOOKUP函數(shù)來查找員工姓名對應(yīng)的員工ID。

公式

=VLOOKUP(A2,A5:D12,1,FALSE)

這次 VLOOKUP 函數(shù)提取數(shù)據(jù)失敗，但返回 #N/A 錯誤。在本例中，我想要查找的數(shù)據(jù) (ID) 位于查找值 (Name) 的左側(cè)。

克服VLOOKUP從左到右查找的局限性

要同時查看右側(cè)和左側(cè)，您可以使用 INDEX 和 MATCH 函數(shù)而不是 VLOOKUP 函數(shù)。

在以下場景中，我嘗試提取員工 ID，并提供員工姓名作為查找值。此處，ID 列位于名稱列的左側(cè)。

句法

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])

公式

=INDEX(A5:D12,MATCH(A2,B5:B12,0),1)

配方分解

MATCH(A2,B5:B12,0)：此處，MATCH 函數(shù)在 B5:B12 范圍內(nèi)查找 James Kim (A2) 的精確匹配項（用 0 表示）。然后，它返回匹配的行號，即 4。

因此，公式變?yōu)?INDEX(A5:D12,4,1)。現(xiàn)在，INDEX 函數(shù)在表數(shù)組 A5:D12 中查找第四列和第一列的交集單元格。交叉單元格是 A8，其中包含 id 號 104。

因此，上式的輸出為104。

第二個限制：VLOOKUP 不能查找多于一列

比方說，我們想要檢索 Bob 從 West 訂購的數(shù)量。這里，我們有 2 位名叫 Bob 的客戶。第一個鮑勃來自北方，第二個鮑勃來自西方。

由于 VLOOKUP 函數(shù)無法基于多個條件工作，因此它只會檢索第一個查找值（來自 North 的 Bob）的數(shù)據(jù)。這就是為什么輸出是 458。

克服VLOOKUP函數(shù)無法查找多列的問題

由于VLOOKUP函數(shù)無法處理多個條件，因此我們需要將多列合并為一列。組合列是輔助列。

在本例中，我使用以下公式將“客戶”和“區(qū)域”列合并到“幫助程序列”中：

公式

=CONCAT(A5,"-",B5)

在這里，我使用 CONCAT 函數(shù)將客戶名稱與其區(qū)域名稱合并，并用破折號 (-) 分隔。

現(xiàn)在，我使用客戶-區(qū)域組合數(shù)據(jù)作為以下 VLOOKUP 公式中的查找值：

公式

=VLOOKUP(A2,C5:E12,3,FALSE)

這次VLOOKUP函數(shù)就可以正常工作了。并且它已檢索到客戶 Bob 從 West 訂購的數(shù)量。所以，輸出是 442。

請記住，這里的表數(shù)組是 C5:E12。所以，它是從輔助欄開始的。由于 VLOOKUP 函數(shù)從表數(shù)組的最左邊的列搜索查找值，因此范圍是從 C 列而不是 A 列開始的。

第三個限制：VLOOKUP 僅限于表數(shù)組的最左列

VLOOKUP 函數(shù)的另一個重要限制是它只能在表數(shù)組的最左列中搜索查找值。否則，它將返回 #N/A 錯誤。

在以下數(shù)據(jù)集中，查找值 (Sarah Lee) 位于表數(shù)組 A5:D12 的第二列中。由于查找值不是表數(shù)組的最左列，因此它返回 #N/A 錯誤。

VLOOKUP 僅限于表數(shù)組最左列的 3 種解決方案

第一種方法

如果查找值不在最左列中，則需要移動表中的列以確保查找值位于表數(shù)組的最左列中。

第二種方法

您可以更改表數(shù)組，使包含查找值的列成為表數(shù)組的第一列。

第三種方法

最后，您可以使用 INDEX-MATCH 公式來執(zhí)行搜索。使用此公式，您可以足夠靈活地選擇查找值和表數(shù)組。

公式

=INDEX(F5:I12,MATCH(F2,G5:G12,0),1)

第四個限制：VLOOKUP 不區(qū)分大小寫

VLOOKUP 的主要限制之一是它無法執(zhí)行區(qū)分大小寫的搜索。看下圖，查找值是正確的大小寫（Korba）。但查找公式返回的是 KOBRA 的工資，且全部大寫。

在這里，Korba 和 KOBRA 并不相同。

克服VLOOKUP區(qū)分大小寫的限制

要使 VLOOKUP 函數(shù)區(qū)分大小寫，必須使用 CHOOSE & EXACT 函數(shù)。

句法

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(lookup_value, lookup_array), return_array), 2, 0)

公式

=VLOOKUP(TRUE, CHOOSE({1,2},EXACT(A2, A5:A12), D5:D12), 2, FALSE)

配方分解

VLOOKUP 函數(shù)的第一個參數(shù)是“TRUE”，這意味著該函數(shù)將搜索 A2 中的查找值與表數(shù)組第一列中的值之間的精確匹配。

VLOOKUP 函數(shù)的第二個參數(shù)是嵌套的 CHOOSE 函數(shù)。 CHOOSE 函數(shù)用于創(chuàng)建兩列的數(shù)組。

=選擇({1,2},精確(A2,A5:A12),D5:D12)

={假,5000;假,3200;假,7500;假,6100;假,2800;假,4600;假,8900;假,3700}

第一列包含 EXACT 函數(shù)的結(jié)果，如果 A2 中的值與范圍 A5:A12 中的任何值匹配，則返回 TRUE，否則返回 FALSE。

=精確（A2，A5：A12）

={假;假;假;假;假;假;假;假}

第二列包含 D5:D12 范圍內(nèi)的值，這些值對應(yīng)于第一列中的每一行。

VLOOKUP函數(shù)的第三個參數(shù)是“2”，這表示該函數(shù)將返回表數(shù)組第二列（即D列）中的值。

VLOOKUP函數(shù)的第四個參數(shù)是“FALSE”，這意味著該函數(shù)只會返回精確匹配，而不返回近似匹配。

總體而言，此公式正在搜索單元格 A2 中的值與 A5:A12 中的一系列值之間的精確匹配，然后返回找到匹配的行的 D 列中的相應(yīng)值。

第 5 個限制：大數(shù)據(jù)集性能低下

處理大型數(shù)據(jù)集時，VLOOKUP 可能會很慢，尤其是在同一工作簿中多次使用時。這是因為 VLOOKUP 必須掃描表的每一行才能找到匹配的值，這可能非常耗時。通常，查找一個范圍大約需要 6.6 秒，查找整個列大約需要 14.2 秒。

為了提高性能，用戶可以使用更快、更高效的替代函數(shù)，例如 INDEX-MATCH 或 XLOOKUP。

第 6 個限制：每次查找僅限一個值

即使表中有多個匹配值，VLOOKUP 每次查找也只能返回一個值。當(dāng)處理重復(fù)值或用戶想要從表中返回多個值時，這可能是一個問題。

解決方案#1：使用公式查找并返回多個值

為了克服此限制，您可以使用以下公式的 TEXTJOIN、IF、IFERROR、MATCH 和 ROW 函數(shù)。此公式可以返回單個單元格內(nèi)的多個值，并用逗號 (,) 分隔。它還會自動消除所有重復(fù)值并僅返回值的第一個實例。

句法

=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(return_array, IF(lookup_value=lookup_array,return_array, ""), 0),"")=MATCH(ROW(return_array),ROW(return_array)),return_array, ""))

公式

=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(C5:C12, IF(A2=A5:A12,C5:C12, ""), 0),"")=MATCH(ROW(C5:C12),ROW(C5:C12)),C5:C12, ""))

解決方案 #2：使用 VBA 查找并返回多個值

如果您不想使用復(fù)雜的公式通過垂直查找返回多個值，可以考慮以下VBA代碼。這段代碼創(chuàng)建了一個名為 LookupMultipleValues 的用戶定義函數(shù)。該函數(shù)只需要3個參數(shù)，使用起來很方便。

Function LookupMultipleValues(gTarget As String, gSearchRange As Range, gColumnNumber As Integer) Dim g As Long Dim k As String For g=1 To gSearchRange.Columns(1).Cells.Count If gSearchRange.Cells(g, 1)=gTarget Then For J=1 To g - 1 If gSearchRange.Cells(J, 1)=gTarget Then If gSearchRange.Cells(J, gColumnNumber)=gSearchRange.Cells(g, gColumnNumber) Then GoTo Skip End If End If Next J k=k & " " & gSearchRange.Cells(g, gColumnNumber) & "," Skip: End If Next g LookupMultipleValues=Left(k, Len(k) - 1) End Function

Visual Basic復(fù)制

使用指南

步驟_1：按 ALT + F11 打開 Visual Basic 編輯器。

步驟_2：轉(zhuǎn)到“插入”>“模塊”。

Step_3：粘貼上面的代碼。

步驟_4：再次返回工作表并使用以下公式。

句法

=LookupMultipleValues(lookup_value,lookup_array,col_index_num)

公式

=LookupMultipleValues(A2,A5:A12,3)

第七個限制：VLOOKUP 函數(shù)在添加或刪除列方面不靈活

當(dāng)您在數(shù)據(jù)區(qū)域中間插入或刪除列時，可能會導(dǎo)致 VLOOKUP 公式出現(xiàn)問題。因為您的 VLOOKUP 引用的列索引號可能不再準(zhǔn)確。

通常，VLOOKUP 函數(shù)的第三個參數(shù)（即列索引號）是硬編碼的。這就是為什么在表數(shù)組中添加或刪除列可能會導(dǎo)致結(jié)果不準(zhǔn)確的原因。

解決 VLOOKUP 關(guān)于添加或刪除列的靈活性問題

為了解決這個問題，可以使用COLUMN函數(shù)來計算列索引號，而不是對其進(jìn)行硬編碼。對于以下場景，這就是使用和不使用 COLUMN 函數(shù)時公式的外觀。

靜態(tài)列索引號

=VLOOKUP(A2,A5:D12,3,FALSE)

動態(tài)列索引號

=VLOOKUP(A2,A5:D12,COLUMN(),FALSE)

要了解更多信息，您可以閱讀 Excel 中 VLOOKUP 計算列索引號的 2 種方法。

第 8 個限制：默認(rèn)情況下，VLOOKUP 返回近似匹配結(jié)果

讓我們記住 VLOOKUP 函數(shù)的語法。

句法

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

您可以看到 VLOOKUP 函數(shù)的第四個參數(shù)是可選的。但 range_lookup 參數(shù)的默認(rèn)設(shè)置是 TRUE – 近似匹配，這是一個問題。因為，如果不指定 range_lookup 參數(shù)，它將執(zhí)行近似匹配。因此，您可能不會得到您想要的結(jié)果。

如果您的數(shù)據(jù)未排序，并且忽略 range_lookup 參數(shù)或?qū)⑵湓O(shè)置為 TRUE-近似匹配，則 VLOOKUP 函數(shù)將不會返回準(zhǔn)確的結(jié)果。

因此，如果您正在查找lookup_value和table_array之間的精確匹配，請確保將range_lookup參數(shù)指定為FALSE – 通過輸入FALSE或0來精確匹配。

Excel中各種查找函數(shù)的比較

功能 優(yōu)點 缺點 索引/匹配可以執(zhí)行從左到右的查找。 更長的公式語法。 X查找支持近似匹配和精確匹配。需要較新版本的 Excel。 查詢可以執(zhí)行水平查找。僅限一排。 錯誤/查找可以處理錯誤并返回替代結(jié)果。僅限于垂直查找。 選擇/匹配可以基于數(shù)字索引執(zhí)行查找。僅限于垂直查找，不如 INDEX/MATCH 公式靈活。

結(jié)論

雖然 VLOOKUP 是 Excel 中用于數(shù)據(jù)分析的強(qiáng)大工具，但它也有其局限性。通過使用我們在本文中概述的最佳解決方案，您可以克服這些限制并將數(shù)據(jù)分析提升到新的水平。無論您選擇使用 INDEX-MATCH、IFERROR、通配符、串聯(lián)還是輔助列，都有多種方法可以充分利用 Excel 電子表格并獲得您需要的結(jié)果。通過一些練習(xí)和實驗，您將能夠掌握這些技術(shù)并立即成為一名 Excel 專家。