Skip to main content

​
A more flexible lookup function — XLOOKUP

In DingTalk Spreadsheet, use the XLOOKUP function to perform lookups and matching. Compared with the existing lookup functions (LOOKUP, VLOOKUP, HLOOKUP), XLOOKUP is more flexible and faster.

​
XLOOKUP overview

The XLOOKUP function searches for a “target value” within a “lookup range”. When the lookup succeeds, it returns the data at the corresponding position in the “result range”, based on the position of the match within the “lookup range”. XLOOKUP has 6 parameters, in order:
  • Target value (required)
  • Lookup range (required)
  • Result range (required)
  • The result to return when the target value is not found
  • Match type
  • Search mode
For detailed parameter descriptions, go to the DingTalk Spreadsheet toolbar Formula > All functions and search for the “XLOOKUP” function.

​
”Lookup range” and “result range” can be anywhere

The existing lookup functions require the “result range” to be to the right of or below the “lookup range”, which can be inconvenient in some scenarios. In the XLOOKUP function, the “lookup range” and the “result range” are independent parameters (the 2nd and 3rd parameters), removing constraints on the structure of the source data: Example: Searching for “Orange” within the range B2:B5 finds the match at position 2, so the result is the 2nd value in the range A2:A5, “dd231”. The XLOOKUP function has no notion of “lookup direction” and requires the “lookup range” to be a single row or single column. The function returns the value at the same position in the result range as the matched row/column. For example: Example: Searching for “Unit price” within the range A1:D1 finds the match at position 3, so the result is the 3rd value in the range A3:D3, 2.3.

​
Default result when no match is found

When the target value is not found, the existing lookup functions return the #N/A error. With XLOOKUP, you can specify a default value (the 4th parameter), which is returned when the target value is not found: Example: If “Cherry” is not found within the range B2:B5, the function returns the 4th parameter as the result.

​
More precise lookup modes

The 5th and 6th parameters of the XLOOKUP function are “match type” and “search mode”. Combine them to specify more precise lookup behavior.
Match type0Default. Only matches values that are exactly equal to the “target value”.
1Prefers values exactly equal to the “target value”. If none exist, returns the smallest value greater than the target value.
-1Prefers values exactly equal to the “target value”. If none exist, returns the largest value smaller than the target value.
2Matches the “target value” using wildcard rules.
Search mode1Default. Matches values in the “lookup range” in order, from first to last.
-1Matches values in the “lookup range” in reverse order.
2Assumes values in the “lookup range” are sorted in ascending order and uses binary search. Faster lookup, but if the “lookup range” is not sorted, the result may be incorrect.
-2Assumes values in the “lookup range” are sorted in descending order and uses binary search. Faster lookup, but if the “lookup range” is not sorted, the result may be incorrect.
For example, find the product closest to the target sales amount: Example: With the 5th parameter set to -1, the function looks for 100 or the largest value smaller than 100. The matching value 77 is found within the range D2:D5, so the corresponding result “Watermelon” is returned.

​
Advanced! Nested use of XLOOKUP

The “result range” of XLOOKUP can span multiple rows and columns. Based on the lookup result, XLOOKUP returns multiple values from the corresponding row/column. Leveraging this feature, the XLOOKUP function can be combined with other functions to handle more scenarios. For example: Example 1 — XLOOKUP combined with SUM: When “Kaola” is located in the 3rd row of the “lookup range”, the XLOOKUP function returns the 3rd row of the “result range”, that is, “30, 40, 50, 60”. The SUM function then sums the results of the XLOOKUP function to produce the final result “180”. Example 2 — Nested XLOOKUP: The inner XLOOKUP function returns the result “30, 40, 50, 60”, which is passed as the “result range” to the outer XLOOKUP function. The outer XLOOKUP function then searches for “Q3” within the range B2:E2. The match is found at position 3, so the result “50” is returned from “30, 40, 50, 60”.

​
Further reading

Similarly, XMATCH also offers more flexible features and faster lookup speeds, and can replace the existing MATCH function. In the DingTalk Spreadsheet toolbar, go to Formula > All functions and search for the “XMATCH” function for detailed parameter descriptions.
Back to DingTalk Spreadsheet