WorksheetFunction.VLookup 方法 (Excel)WorksheetFunction.VLookup method (Excel)

在表格陣列的第一欄中搜尋某個數值,並傳回該表格陣列中同一列之其他欄中的數值。Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.


運算式VLookup(Arg1Arg2Arg3Arg4)expression.VLookup (Arg1, Arg2, Arg3, Arg4)

_運算式_代表**WorksheetFunction** 物件的變數。expression A variable that represents a WorksheetFunction object.


名稱Name 必要/選用Required/Optional 資料類型Data type 描述Description
Arg1Arg1 必要Required VariantVariant Lookup_value - 要在表格陣列的第一欄中搜尋的值。Lookup_value - the value to search in the first column of the table array. lookup_value 可以是數值,也可以是參照。Lookup_value can be a value or a reference. 如果 lookup_value 比 table_array 第一欄中的最小值還小, VLookup會傳回 # n/A 錯誤值。If lookup_value is smaller than the smallest value in the first column of table_array, VLookup returns the #N/A error value.
Arg2Arg2 必要Required VariantVariant Table_array - 兩個以上的資料欄。Table_array - two or more columns of data. 使用範圍參照或範圍名稱。Use a reference to a range or a range name. table_array 中第一欄的值就是 lookup_value 所搜尋的值。The values in the first column of table_array are the values searched by lookup_value. 這些值可以是文字、數字或邏輯值。These values can be text, numbers, or logical values. 不區分大小寫的文字。Uppercase and lowercase text are equivalent.
Arg3Arg3 必要Required VariantVariant Col_index_num - 要傳回相符值的來源table_array 中的欄號。Col_index_num - the column number in table_array from which the matching value must be returned. col_index_num 為 1 會傳回 table_array 中第一欄的值,col_index_num 為 2 會傳回 table_array 中第二欄的值,依此類推。A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.
Arg4Arg4 選用Optional VariantVariant Range_lookup-邏輯值,指定是否要VLookup方法尋找完全相符或近似相符。Range_lookup - a logical value that specifies whether you want the VLookup method to find an exact match or an approximate match.

傳回值Return value



VLookup 中的 V 代表垂直。The V in VLookup stands for vertical. 當您的比較值位於要尋找的資料左方的某一欄中時,使用 VLookup 方法來取代 HLookup 方法。Use the VLookup method instead of the HLookup method when your comparison values are located in a column to the left of the data that you want to find.

如果 Col_index_num 小於 1,VLookup 方法會產生錯誤。If Col_index_num is less than 1, the VLookup method generates an error.

如果 Col_index_num 大於 table_array 的欄數,VLookup 方法會產生錯誤。If Col_index_num is greater than the number of columns in table_array, the VLookup method generates an error.

如果 Range_lookup 為True或省略,則會傳回完全相符或近似相符項目。If Range_lookup is True or omitted, an exact or approximate match is returned. 如果找不到完全相符的值時,會傳回大小僅次於 lookup_value 的值。If an exact match is not found, the next largest value that is less than lookup_value is returned. table_array 的第一欄中的值必須以遞增順序排列;否則, VLookup 方法可能無法提供正確的值。The values in the first column of table_array must be placed in ascending sort order; otherwise, the VLookup method may not give the correct value.

如果 Range_lookup 為FalseVLookup方法只會尋找完全相符。If Range_lookup is False, the VLookup method will only find an exact match. 在這種情況下,table_array 第一欄中的值便不需要排序。In this case, the values in the first column of table_array do not need to be sorted. 如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符,則會使用第一個找到的值。If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. 如果找不到完全相符的值,則會產生錯誤。If an exact match is not found, an error is generated.

當 table_array 第一欄中搜尋文字值,請確定 table_array 第一欄中的資料,並沒有前置空格後, 置空格,不一致使用直線 ('") 和捲曲 () 引號標記或非列印字元。When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight (' or ") and curly ( or ) quotation marks, or nonprinting characters. 在這些情況下, VLookup方法可能會提供不正確或非預期的值。In these cases, the VLookup method may give an incorrect or unexpected value. 如需如何清除或修剪值的詳細資訊,請參閱 CleanTrim 方法。For information about how to clean or trim values, see the Clean and Trim methods.

在搜尋數字或日期值時,請確認沒有將 table_array 第一欄中的資料儲存成文字值。When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. 否則在這種情況下,VLookup 方法可能會提供不正確或非預期的值。In this case, the VLookup method may give an incorrect or unexpected value.

如果 range_lookup 為False而 lookup_value 為文字,您可以在 lookup_value 中使用萬用字元、 問號 (?) 和星號 (*)。If range_lookup is False and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. 問號代表任一單一字元,而星號代表任何連續字元。A question mark matches any single character; an asterisk matches any sequence of characters. 如果想要尋找實際的問號及星號,請在該字元前面輸入波狀符號 (~)。If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

支援和意見反應Support and feedback

有關於 Office VBA 或這份文件的問題或意見反應嗎?Have questions or feedback about Office VBA or this documentation? 如需取得支援服務並提供意見反應的相關指導,請參閱 Office VBA 支援與意見反應Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.