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

搜尋表格的第一列或值的陣列中的值並則會傳回值在同一欄從您指定的表格或陣列中的資料列。Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row that you specify in the table or array. 當您比較的值位於列中最上面的資料表的資料,而您想要尋找關閉指定的列數,請使用vlookup 函數Use HLookup when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. 當您比較的值位於左側的 [您想要尋找的資料欄中,請使用**VLookup** 。Use VLookup when your comparison values are located in a column to the left of the data that you want to find.


運算式Vlookup 函數(Arg1Arg2Arg3Arg4)expression.HLookup (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 be found in the first row of the table. Lookup_value 可以是數值、參照或文字字串。Lookup_value can be a value, a reference, or a text string.
Arg2Arg2 必要Required VariantVariant Table_array - 要在其中搜尋資料的資訊表格。Table_array - a table of information in which data is looked up. 使用範圍參照或範圍名稱。Use a reference to a range or a range name.
Arg3Arg3 必要Required VariantVariant Row_index_num - 要傳回相符值的來源 table_array 中的列號。Row_index_num - the row number in table_array from which the matching value will be returned. row_index_num 為 1 會傳回 table_array 中第一列的值,row_index_num 為 2 會傳回 table_array 中第二列的值,依此類推。A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. 如果 row_index_num 小於 1, vlookup 函數會傳回 #VALUE !If row_index_num is less than 1, HLookup returns the #VALUE! 錯誤值。如果 row_index_num 大於 table_array 上的列數, vlookup 函數會傳回 # #REF !error value; if row_index_num is greater than the number of rows on table_array, HLookup returns the #REF! 錯誤值。error value.
Arg4Arg4 選用Optional VariantVariant Range_lookup-邏輯值,指定是否要vlookup 函數來尋找完全相符或近似相符。Range_lookup - a logical value that specifies whether you want HLookup to find an exact match or an approximate match. 如果為 True或省略,則會傳回近似相符。If True or omitted, an approximate match is returned. 換句話說,如果找不到完全相符的值時,會傳回僅次於 lookup_value 的值。In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. 如果為 FalseHLookup會尋找完全相符。If False, HLookup will find an exact match. 如果找不到完全相符的值,會傳回 #N/A 錯誤值。If one is not found, the error value #N/A is returned.

傳回值Return value



如果HLookup找不到 lookup_value,並且 range_lookup 為True,它會使用僅次於 lookup_value 的最大值。If HLookup can't find lookup_value, and range_lookup is True, it uses the largest value that is less than lookup_value.

如果 lookup_value 比 table_array 第一列中的最小值還小, HLookup會傳回 # n/A 錯誤值。If lookup_value is smaller than the smallest value in the first row of table_array, HLookup returns the #N/A error 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 (~) before 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.