兩個工作簿之間的 VLOOKUP：簡單的分步指南

如果您在 Excel 中處理多個工作簿，您可能需要使用 VLOOKUP 函數將數據從一個工作簿檢索到另一個工作簿。

VLOOKUP 函數在單獨工作簿的表中搜索特定值，並從同一行返回相應的值。語法必須包括對第二個工作簿和工作表名稱的完全限定引用。

當您第一次使用該功能時，獲得正確的參考可能具有挑戰性。本文使用示例電子表格向您展示如何輸入公式。

您還將了解 VLOOKUP 的一些增強功能和該函數的幾個替代方法。

讓我們開始吧！

目錄

• 什麼是 VLOOKUP？
• 如何在兩個工作簿之間使用 VLOOKUP
  • 樣本數據
  • 第 1 步：保持兩個工作簿都打開
  • 第 2 步：開始輸入 VLOOKUP 公式
  • 第 3 步：如何引用第二個工作簿
• 3 個常見的 VLOOKUP 錯誤及其修復方法
  • 1. #N/A 錯誤
  • 2. #參考！列號錯誤
  • 3.引號和方括號錯誤
• 管理不同工作簿的 4 個技巧
• 如何自動更新數據
• VLOOKUP 函數的高級用法
• VLOOKUP 的 2 個主要替代品
  • 1. 指數匹配
  • 2.XLOOKUP
• 如何跨多個工作表使用 VLOOKUP
  • 第 1 步：識別組件
  • 第 2 步：設置您的數據
  • 第 3 步：使用 VLOOKUP 函數
  • （可選）第 4 步：排除#N/A 錯誤
• 最後的想法

什麼是 VLOOKUP？

假設您有一個產品銷售工作簿和一個包含每種產品價格的單獨工作簿。您想要訪問第二個工作簿中的價格並在第一個工作簿中的計算中使用它們。

VLOOKUP是此任務的一種解決方案。該術語代表“垂直查找”。“

它是一個內置函數，允許您在列中搜索特定值並根據位置在另一列中返回相應的值。

它可以用於：

• 一個電子表格

• 同一工作簿中的多個工作表

• 兩個 Excel 工作簿

如果您沒有多個工作簿的額外複雜性，請查看本教程，了解如何使用 VLOOKUP 比較 Excel 中的兩列。

如何在兩個工作簿之間使用 VLOOKUP

當您需要跨多個工作表或工作簿搜索數據時，VLOOKUP 函數特別有用。

這樣做的三個主要步驟是：

1. 打開兩個工作簿。

2. 在第一個 Excel 工作簿中輸入 VLOOKUP 公式。

3. 提供對第二個工作簿的完全限定參考。

樣本數據

這個簡單的教程通過基於示例數據的示例來工作。接下來，創建一個名為“Sales”的新工作簿，並在 A 列和 B 列中輸入此數據：

• 蘋果，40

• 橘子，30

• 梨，70

• 香蕉，20

添加第三列來保存價格信息：

創建第二個名為“Product”的 Excel 工作簿，並將 Sheet1 重命名為“Prices”。在 A 列和 B 列中輸入此數據：

• 蘋果，1.50

• 橙子，2.30

• 梨，3.60

• 香蕉，2.10

數據如下所示：

要計算第一個電子表格中的銷售收入，我們需要在第二個電子表格中查找價格。VLOOKUP 助您一臂之力！

第 1 步：保持兩個工作簿都打開

在兩個工作簿之間使用 VLOOKUP 公式時，兩個工作簿都必須打開。如果關閉第二個工作簿，該函數將返回錯誤。

您可以單獨打開它們，也可以使用 Excel 中的“打開”命令同時打開它們。有兩種方法可以打開任一工作簿：

• 轉到“文件”選項卡並選擇“打開”。

• 使用“Ctrl + O”鍵盤快捷鍵。

打開兩個工作簿後，您可以通過單擊 Excel 窗口底部的相應選項卡在它們之間切換。

第 2 步：開始輸入 VLOOKUP 公式

下面是 VLOOKUP 函數的基本語法：

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

• lookup_value：您要在其他工作簿中查找的值。

• table_array：另一個工作簿中包含您要檢索的數據的單元格區域。

• col_index_num：包含要檢索的數據的範圍內的列號。

• range_lookup：最後一個參數是可選的。如果將其設置為 TRUE 或省略它，VLOOKUP 將查找近似匹配項。如果將其設置為 FALSE，VLOOKUP 將只查找完全匹配。

假設您想在示例數據的第二個工作簿中查找蘋果的價格。這就是公式分解的方式：

• lookup_value 位於第一個工作簿的單元格 A2 中。

• 表數組位於第二個工作簿的單元格區域 A1:B5 中。

• col_index_num 是保存價格的第二列。

VLOOKUP 公式中最複雜的部分是正確引用第二個工作簿。

第 3 步：如何引用第二個工作簿

要完成兩個工作簿的 VLOOKUP 公式，您需要使用包含第二個工作簿中的這些詳細信息的完全限定參考：

• 工作簿名稱

• 工作表名稱

• 單元格範圍

這可確保 Excel 確切知道在哪裡可以找到您要查找的數據。完全限定引用用於 VLOOKUP 函數的 table_array 參數。

這些是示例數據的值：

• 工作簿名稱：Product.xlsx

• 工作表名稱：價格

• 單元格範圍：$A1:$B5

要查找第一項的價格，請在第一個工作簿的單元格 C2 中輸入以下公式：

=VLOOKUP(A2, [Product.xlsx]價格！$A$1:$B$5, 2, FALSE)

此圖片顯示了第一個單元格的公式欄中的實際功能：

請注意，工作簿名稱括在方括號中，工作表名稱後跟一個感嘆號。

如果 Excel 文件名包含空格或特殊字符，則必須將整個工作簿和工作表括在單引號中。

該公式使用單元格區域的絕對值（$ 前綴）。這允許您通過單擊填充柄將公式複製到其餘行。

以下是 VLOOKUP 結果：

就是這樣！您現在知道如何有效地使用 VLOOKUP 了。為了在您遇到任何錯誤時為您提供幫助，我們在下一節中匯總了三個最常見的 VLOOKUP 錯誤。

3 個常見的 VLOOKUP 錯誤及其修復方法

現在您已經了解瞭如何使用另一個工作簿中的 VLOOKUP，您可能會遇到一些常見錯誤。

以下是三個最常見的錯誤以及如何修復它們。

1. #N/A 錯誤

當在表數組中找不到查找值時，會發生 #N/A 錯誤。要修復此錯誤，您可以嘗試以下操作：

• 檢查查找值的拼寫。

• 確保查找值位於表數組的第一列中。

• 檢查查找值是否未被格式化為兩個工作表中的文本。

• 確保將 range_lookup 參數設置為 FALSE 以查找精確匹配。

上面的示例未能在第二個工作簿中找到 Cherries 的匹配值。

2. #參考！列號錯誤

#參考！當 col_index_num 參數大於表數組中的列數時會發生錯誤。

要修復此錯誤，請檢查 col_index_num 參數是否小於或等於源表中的列數。

在下圖中，col_index_num 已設置為 3，但參考範圍內只有兩列。

3.引號和方括號錯誤

當您使用包含引號或方括號的值執行 VLOOKUP 時，會發生引號和方括號錯誤。

要修復此錯誤，您可以嘗試以下操作：

• 在查找值周圍使用雙引號。

• 使用CHAR(34)函數插入雙引號。

• 如果查找值包含引號或方括號，請使用 IFERROR 函數返回空白單元格。

一旦您熟悉了這些錯誤，修復它們就很簡單了！為了進一步幫助您成為 VLOOKUP 專家，我們在下一節中提供了四個管理不同工作簿的技巧。

管理不同工作簿的 4 個技巧

使用多個工作簿時，跟踪哪個工作簿包含哪些數據很重要。

以下是保持理智的四個最佳提示：

1. 使用命名約定幫助您記住哪些 Excel 電子表格包含哪些數據。

2. 在公式中使用絕對引用以確保查找範圍引用正確的單元格，即使您將公式移動或複製到其他位置或 Excel 工作表也是如此。

3. 使用 IFERROR 函數處理在源數據中未找到查找值時可能發生的任何 #N/A 錯誤。

4. 考慮鎖定參考工作簿中的單元格，以免它們被覆蓋。

如何自動更新數據

使用多個 Excel 文件時，手動更新數據可能會很耗時。

如果您希望 VLOOKUP 公式在您從其他工作簿中添加或刪除數據時自動更新，您可以使用 INDIRECT 函數。

就是這樣：

• 在 VLOOKUP 公式中，將表數組參數替換為 INDIRECT 函數。

• 在 INDIRECT 函數中，使用對包含其他工作簿中表數組的單元格的引用。

要使用我們的示例工作簿實現此目的，請按照以下步驟操作。

1. 使用 D 列中的一個單元格輸入對“產品”工作簿的“價格”表的引用和單元格範圍 A1:B5，如下所示：[Product.xlsx]Prices!A:B

2. 在“銷售”工作簿的單元格 C2 中，使用此公式：=VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. 向下複製公式（絕對引用確保保留電子表格的單元格）。

這些操作確保原始工作表中的更改在連接的單元格中自動更新。

此圖顯示了結合 VLOOKUP 的 INDIRECT 函數：

請注意，此示例使用範圍“A:B”來引用兩列。這意味著當您添加更多產品和價格時，該公式將繼續在當前工作簿中起作用。

現在您已經領略了 VLOOKUP 的強大功能，讓我們在本節中了解該函數的一些高級用法！

VLOOKUP 函數的高級用法

本文中的示例為每個查找值返回一個匹配項。如果要處理來自不同工作簿的多個匹配項，則可以改用數組公式。

您還可以將 VLOOKUP 與其他函數結合使用。例如，您可以將其嵌入 IF 函數中以進行額外的複雜分析。

複雜分析包括卡方檢驗和配對樣本 T 檢驗等技術。下面是在 Excel 中使用卡方檢驗的示例：

高級用戶利用 VLOOKUP 比較數據集並識別不匹配項。您將在本文中找到有關在 Excel 中查找差異的其他方法。

但是，如果您出於某種原因不想使用 VLOOKUP 怎麼辦？你很幸運！有一些替代方案，我們將在下一節中介紹。

VLOOKUP 的 2 個主要替代品

在不同工作表中查找值時，VLOOKUP 通常是首選函數。但是，有一些替代方案在某些情況下可能很有用。

最常見的兩種是：

• 索引匹配

• XLOOKUP

1. 指數匹配

INDEX 和 MATCH 是兩個函數的強大組合，可用於在表中查找值。與 VLOOKUP 要求查找值位於表的最左側列不同，INDEX MATCH 可以在任何列中查找值。

它是這樣工作的：

1. INDEX 函數根據行號和列號返回指定範圍內的值。

2. MATCH 函數返回查找值在指定範圍內的位置。

要使用 INDEX MATCH，您需要指定要搜索的範圍、要查找查找值的行以及要返回結果的列。

這是一個示例公式：

=INDEX(TableRange,MATCH(LookupValue,LookupRange,0),ColumnIndex)

要將其與示例電子表格一起使用，請使用以下公式：

=INDEX([Product.xlsx]價格！$B$1:$B$5, MATCH(A2, [Product.xlsx]價格!$A$1:$A$5, 0))

這分解為：

• MATCH 函數在“產品”工作簿的“價格”表的 A1:A5 範圍內搜索單元格 A2（產品名稱）中的值。

• INDEX 函數根據 MATCH 函數提供的行號返回“產品”工作簿“價格”表中 B1:B5 範圍內的值。

這張圖片顯示了正在運行的公式：

2.XLOOKUP

XLOOKUP 是Excel 365中引入的較新函數。它類似於 VLOOKUP，但具有一些附加功能。

XLOOKUP 的最大優點之一是它可以查找查找列左側的值。

它是這樣工作的：

1. XLOOKUP 函數根據查找值返回指定範圍內的值。

2. 您可以指定是要精確匹配還是近似匹配。

3. 如果找不到匹配項，您還可以指定要返回的內容。

要使用 XLOOKUP，您需要指定查找值、要搜索的範圍以及要返回結果的列。

這是一個示例公式：

=XLOOKUP(LookupValue,LookupRange,ReturnRange,”Not Found”,0)

要將其與示例電子表格一起使用，請使用以下公式：

=XLOOKUP(A2, [Product.xlsx]價格！$A$1:$A$5, [Product.xlsx]價格!$B$1:$B$5)

這分解為：

• A2：您要查找的值（產品名稱）。

• [Product.xlsx]Prices!$A$1:$A$5：要搜索查找值的範圍（“產品”工作簿的“價格”表中的產品名稱）。

• [Product.xlsx]Prices!$B$1:$B$5：包含返回值的範圍（“Product”工作簿的“Prices”表中的價格）。

這是實際的公式：

如何跨多個工作表使用 VLOOKUP

如果需要在兩個工作簿之間執行 VLOOKUP，則可能還需要跨多個工作表使用 VLOOKUP。

幸運的是，這樣做的過程與在兩個工作簿之間執行 VLOOKUP 非常相似。

有四個步驟。

第 1 步：識別組件

您需要包括工作表名稱，而不是像對一張工作表那樣包括表格數組。

例如，如果您想要在 Sales_reports.xlsx 工作簿的 Jan 工作表中搜索 A2:B6 範圍內的值，您的公式將如下所示：

=VLOOKUP(A2,[Sales_reports.xlsx]1 月！$A$2:$B$6,2,FALSE)

第 2 步：設置您的數據

確保您的數據以一種便於跨多個工作表引用的方式組織。

您可能需要考慮為特定參考數據創建不同的選項卡。

第 3 步：使用 VLOOKUP 函數

設置數據後，您可以使用 VLOOKUP 函數從其他工作表中搜索和檢索數據。

只需確保在您的公式中包含工作表名稱，如上所示。

（可選）第 4 步：排除#N/A 錯誤

如果您在跨多個工作表使用 VLOOKUP 時遇到 #N/A 錯誤，請仔細檢查您的公式以確保您已正確引用所有必要的工作表和範圍。

您可能還想檢查您執行的是精確匹配，而不是范圍查找。

最後的想法

您已經學習瞭如何使用 VLOOKUP 在兩個電子表格中搜索和檢索數據。這種多功能的功能使您可以根據另一個工作簿中的相應值輕鬆查找一個工作簿中的值。

VLOOKUP 函數的功能遠遠超出了單個工作表，可以跨不同的工作簿鏈接和分析數據。

掌握此功能是精通 Excel 的重要一步，因為它為管理和解釋數據開闢了新的可能性。

通過合併 INDIRECT、INDEX-MATCH 或 XLOOKUP 等函數，您可以進一步增強數據查找的靈活性和有效性。

不斷練習和探索 Excel 的強大功能；這只是冰山一角。快樂的數據搜索！