說明
Use this function to search a range or an array, and return an item corresponding to the first match it finds.
語法
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
lookup_value |
是 | The value to search for. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
lookup_array |
是 | The range or array to search within. | A cell range or a formula which results in a cell range. |
return_array |
是 | The range or array to return the value from. | A cell range or a formula which results in a cell range. |
if_not_found |
沒有 | The value to return if no match is found. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
match_mode |
沒有 | How the match is to be made. | 0 for exact match (default), -1 for exact match or next smaller item, 1 for exact match or next larger item, 2 for wildcard match. |
search_mode |
沒有 | The search mode and order. | 1 to search from first to last (default), -1 to search from last to first, 2 to search a binary search in ascending order, -2 to search a binary search in descending order. |
範例
範例資料
| A | B |
|---|---|
| Apple | $1.25 |
| Banana | $0.75 |
| Cherry | $2.50 |
| 日期 | $3.00 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=XLOOKUP("Banana", A2:A5, B2:B5) |
Finds the price of "Banana". | $0.75 |
=XLOOKUP("Cherry", A2:A5, B2:B5, "Not Found") |
Finds the price of "Cherry". | $2.50 |
=XLOOKUP("Grape", A2:A5, B2:B5, "Not Found") |
Searches for "Grape" and returns "Not Found" if not found. | 找不到 |
=XLOOKUP("Date", A2:A5, B2:B5, , , -1) |
Finds the price of "Date" by searching from the last to first. | $3.00 |
附註
- XLOOKUP can search both vertically and horizontally.
- If there is no match and no
if_not_foundvalue is specified, XLOOKUP returns a #N/A error. - XLOOKUP can replace older functions such as VLOOKUP and HLOOKUP.
- Wildcards such as * and ? can be used in
lookup_valuewhenmatch_modeis set to 2.
提示
- Use XLOOKUP to simplify your lookup formulas, as it offers more functionality and flexibility than older lookup functions.
- Consider using the
if_not_foundparameter to handle missing data gracefully. - Use XLOOKUP in combination with other functions to create more complex formulas and data retrieval methods.