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。