XLOOKUP
函式會在查詢範圍中尋找相符項目,然後傳回該相符項目在結果範圍中的對應值。如果找不到相符項目,系統會傳回最接近的值。交叉查詢。在搜尋欄中尋找相符項目,然後傳回該相符項目在指定資料欄中的對應值。
用法示範
=XLOOKUP("Apple",table_name!fruit,table_name!price)
語法
XLOOKUP(搜尋值,查詢範圍,結果範圍,無結果時的傳回值,比對模式)
搜尋值
:要搜尋的值。例如:42
、"Cats"
或B24
。搜尋欄
:搜尋值所在的資料欄。結果欄
:結果值所在的資料欄。無結果時的傳回值
:[選用 - 預設是#N/A
] 找不到相符項目時要傳回的值。比對模式
:[選用 - 預設是0
] 尋找與搜尋值相符之項目的比對方式。0
:代表完全比對。1
:代表完全比對,或尋找大於搜尋值的最接近值。-1
:代表完全比對,或尋找小於搜尋值的最接近值。2
:代表以萬用字元進行比對。
提示:BigQuery 適用的 XLOOKUP 不支援搜尋模式
。
用法示範
XLOOKUP("Apple", A2:A, E2:E)
可取代 VLOOKUP("Apple", A2:E, 5, FALSE)
XLOOKUP("Price", A1:E1, A6:E6)
可取代 HLOOKUP("Price", A1:E6, 6, FALSE)
XLOOKUP
:當相符項目資料欄位於輸出結果資料欄的右側時XLOOKUP("Apple", E2:E7, A2:A7)
:其相等的 VLOOKUP
函式為 VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE)
語法
XLOOKUP(搜尋值, 查詢範圍, 結果範圍, 無結果時的傳回值, 比對模式, 搜尋模式)
搜尋值
:要搜尋的值。例如:42
、"Cats"
或B24
。查詢範圍
:要搜尋的範圍。這個範圍必須是單一列或欄。結果範圍
:結果值所在的範圍。依查詢方式而定,這個範圍的列或欄大小應該要和查詢範圍
相同。無結果時的傳回值
:[選用 - 預設是#N/A
] 找不到相符項目時要傳回的值。比對模式
:[選用 - 預設是0
] 尋找與搜尋值
相符之項目的比對方式。0
代表完全比對。1
代表完全比對,或尋找大於搜尋值
的最接近值。-1
代表完全比對,或尋找小於搜尋值
的最接近值。2
代表以萬用字元進行比對。
搜尋模式
:[選用 - 預設是1
] 搜尋查詢範圍
的方式。1
代表從第一個項目開始逐一搜尋到最後一個項目。-1
代表從最後一個項目開始逐一搜尋到第一個項目。2
代表以二分法搜尋整個範圍,且必須先以遞增方式排序該範圍。-2
代表以二分法搜尋整個範圍,且必須先以遞減方式排序該範圍。
附註
- 如果
結果範圍
包含多個資料列或資料欄,則輸出結果會是查詢範圍
中相符項目所在位置的一整列/整欄資料。
範例
本文所有範例查詢時所用的資料表。
XLOOKUP
查詢總銷售金額 (省略比對模式
和搜尋模式
,但指定了「無結果時的傳回值」這個引數)。以
XLOOKUP
查詢總銷售金額 (比對模式 = 0
,搜尋模式 = 1
和 -1
)。以
XLOOKUP
查詢總銷售金額 (比對模式 = 1
和 -1
,且省略搜尋模式
)。讓
XLOOKUP
以橫向比對方式尋找值,並傳回一整欄資料。