About This Article
This article was created using an automated generation workflow powered by generative AI. Based on Microsoft's official XLOOKUP documentation, it explains how to specify the search direction to return either the first or last match for duplicate codes. The functionality in local Excel environments or the example formulas has not been verified on actual hardware.
Verification Status: Specification checked (spec_checked). The specification has been verified against official documentation. Execution of the formula examples has not been verified.
- Determining Which Row's Value to Retrieve from the Same Code
- Visualizing search direction and returned rows
- Reading the formula by aligning argument correspondence
- Search modes other than exact match from start or end
- Checklist when expected values are not returned
- Things to decide before using it in operations
- Summary
- Official Information and Primary Sources
- Sample code
Determining Which Row's Value to Retrieve from the Same Code
In tables where product codes or employee codes appear across multiple rows, the values returned for the same search value may differ between the first and last rows. By default, XLOOKUP scans from the beginning of the search range and returns the first match found. To use the match located at the end, set the search mode to search from the last.
In the following example, the "Status" corresponding to the code A-102 is retrieved. Assume the table starts on row 2, with the code in column A and the status in column B. The search value A-102 is entered in cell E2.
| Row | Code (Column A) | Status (Column B) |
|---|---|---|
| 2 | A-101 | Accepted |
| 3 | A-102 | Under Review |
| 4 | A-103 | Completed |
| 5 | A-102 | Shipped |
The formula to return the first match is as follows:
=XLOOKUP(E2,A2:A5,B2:B5,"該当なし",0,1)
The formula to return the last match sets the final argument to -1.
=XLOOKUP(E2,A2:A5,B2:B5,"該当なし",0,-1)
Expected outcome (unexecuted): When E2 is A-102, the search mode 1 is expected to return "Pending", and -1 is expected to return "Shipped". The "last" match here refers to the lowest matching row in the current search range. It does not automatically determine the latest date or the final business process in the data.
Visualizing search direction and returned rows
Even with the same search value, the search direction changes which match is selected. The following diagram illustrates how the corresponding value in column B is returned when scanning the range from top to bottom versus searching upward from the bottom.
flowchart TB
A["検索値 A-102"] --> B{"検索方向"}
B -->|"1: 先頭から"| C["A2:A5を上から確認"]
B -->|"-1: 末尾から"| D["A2:A5を下から確認"]
C --> E["最初の一致: 行3"]
D --> F["最後の一致: 行5"]
E --> G["対応するB列: 確認中"]
F --> H["対応するB列: 出荷済み"]
1 finds row 3 first, while -1 searches from the bottom and finds row 5 first. XLOOKUP returns the value from the return range corresponding to the matched row. The diagram and results are explanations based on official specifications and are not the result of actually operating this workbook.
Reading the formula by aligning argument correspondence
The arguments for XLOOKUP are in the following order:
XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
In this formula, E2 is the lookup value, A2:A5 is the lookup range for finding the code, and B2:B5 is the return range from which to retrieve the value. "該当なし" is the value to display if the code is not found, 0 specifies an exact match, and the final 1 or -1 represents the search direction.
The lookup range and return range must match not only in the number of rows but also in their row positions.A2:A5 If searching row B2:B5, the return range must also correspond to the same row in
First, enter A-102 into E2 and input the formula. Next, change only the final argument of the formula from 1 to -1. If the lookup value and range are kept fixed, the difference in return values can be compared as a difference in search direction. By copying and placing the formulas side by side, you can view both results simultaneously.
Expected Example (Not Executed): Changing E2 to A-103, which exists only once, is expected to result in "Complete" regardless of whether the search mode is 1 or -1. Comparing how the return values differ only when duplicates exist shows that the search mode determines which value to return from multiple matching candidates rather than whether a match exists.
Search modes other than exact match from start or end
If the search mode is omitted, XLOOKUP searches from the first item. Explicitly writing 1 leaves the intent clear in the formula, allowing anyone reviewing it later to determine the search direction without relying on default behavior. Choosing -1 to find the last match is useful when you need to search from the bottom without changing the data order.
Search modes 2 and -2 specify a binary search. Binary search is a method that narrows down the search range by half each time. Microsoft official documentation explains that 2 assumes ascending order and -2 assumes descending order, and that unvalidated results may occur if the range is not sorted. If you only need to match the first or last item, 1 or -1, which do not assume sorting, can make your intent clearer.
When using a binary search, verify that the search range is always maintained in the specified order. Sorting the source data may also affect other calculations and review processes that rely on row order. If you adopt a binary search for performance or other reasons, ensure both that the order is maintained and that the returned results align with the operational meaning of the sorting.
Checklist when expected values are not returned
If the formula results in an error or returns a value from an unintended row, verify the following points in order:
Check the rows of the lookup range and return range
A2:A5Check whether corresponding rows align, such as inB2:B5andCheck the actual lookup value and code data
Even if codes look identical, one may be stored as a number and the other as a text string. Additionally, leading or trailing spaces in codes can prevent the expected exact match. Rather than performing a bulk conversion on the original column right away, compare values and data types in a copied range, and prepare normalized codes in a separate column for comparison if necessary.Check if the search mode is in the correct argument position
1/-1is the sixth argument. If its position is swapped with the fifth match mode0, the search direction will not be as intended. Verify whether the latter half of the expression is...,0,1or...,0,-1.Check formula delimiters and Excel compatibility
The delimiter used to separate formula arguments may vary depending on regional settings. Although commas are used in the examples, if an error occurs during input, check the delimiter used in your local version of Excel. Additionally, official documentation states that XLOOKUP is available in Excel for Microsoft 365, Excel 2021, and later, and is not supported in Excel 2016 and Excel 2019. Confirm whether the function is recognized not only in the environment where the formula is entered, but also by users with whom the file is shared.Define what "last" means
-1returns the last matching row found within the lookup range. Unless it is guaranteed that the rows are kept in chronological order, "match from last" and "latest match" are not the same thing. In workflows that use the latest records, separately confirm whether the update timestamp column or sorting rules are maintained.
Things to decide before using it in operations
In tables where the same code appears in multiple rows, such as reception histories, establish an operational rule on whether to use the first state or the last state. To avoid a situation where only the person who entered the formula understands the intent, explicitly specify the search mode and leave a note in the header or adjacent cell indicating the meaning of the return value, such as "first row" or "last row in the current sort order," to facilitate handovers.
In particular, if the table is sorted later, the result of -1 may also change. Test which row should be returned after sorting using dummy duplicate codes before applying it to the actual table. In shared workbooks, considering that users with edit permissions might change the original sort order or code column, it is also effective to decide to set up necessary confirmation fields and update procedures rather than finalizing processing based solely on formula results.
If shared recipients include versions that do not support XLOOKUP, you must choose a search method available in the target Excel distribution. Function compatibility should be verified not only in the creator's environment, but also in the environments of those viewing and editing it. Since alternative formulas are not shown here, if backward compatibility with older versions is a requirement, verify it separately after determining the target versions.
Summary
Specify 1 as the search mode to return the first row of duplicate codes, or -1 to return the last row. Compare cases with duplicates and single entries in a small table, verify the correspondence between the lookup range and return range, the table sort order, and the business meaning of the return value before applying it to actual data. Since binary search 2 / -2 assumes sorting, evaluate it separately from use cases for choosing first and last matches.
