Knowledge Library
Learn How to Master XLOOKUP on Micrsoft Excel Now!
Use the XLOOKUP function when you need to find things in a table or a range by row and column. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.
The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP. For example, XLOOKUP can look to its left, defaults to an exact match, and allows you to specify a range of cells instead of a column number.
XLOOKUP Syntax Explained
The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
|
Argument |
Description |
|---|---|
|
lookup_value Required |
The lookup value |
|
lookup_array Required |
The array or range to search |
|
return_array Required |
The array or range to return |
|
[if_not_found] Optional |
Where a valid match is not found, return the [if_not_found] text you supply. Remember to add ” ” in between your return text to display the text you have inserted. If a valid match is not found, and [if_not_found] is missing, #N/A will be returned. |
當您需要在表格或範圍依列尋找專案時，請使用 XLOOKUP函數。 有了 XLOOKUP，不論傳回資料行在哪一側，您可以在一欄中尋找搜尋字詞，並傳回另一個資料列中相同資料列的結果。
新的 XLOOKUP 函數提升了 VLOOKUP 和 HLOOKUP 在搜索時的靈活度。 例如，XLOOKUP可以向左搜索，默認為完全匹配，並允許您指定單元搜尋格範圍，而非數據表格的欄或列的順位號。
XLOOKUP 語法解說
XLOOKUP 函數搜尋範圍或陣列，並傳回對應至所找到的第一個相符專案。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
|
引數 |
描述 |
|---|---|
|
lookup_value 必要 |
查閱值 |
|
lookup_array 必要 |
要搜尋的陣列或範圍 |
|
return_array 必要 |
要傳回的陣列或範圍 |
|
[if_not_found] 選用 |
找不到有效的相符，請傳回您所提供的 [if_not_found] 文字。 請記得把“”加到提供的 [if_not_found] 文字以顯示你所輸入的文字。 如果找不到有效的相符，且遺失 [if_not_found]，則會傳回 #N/A。 |