In most cases, the binary search option is not useful
The XLOOKUP function uses a sophisticated linear search method by default. It is my own guess that Excel simply sorts the lookup array before performing a binary search when either of the first two options are chosen.
Microsoft have stated that there is no significant speed benefit in using the binary search option.
Logically the binary options should be slightly faster as they save Excel the trouble of sorting the lookup array in memory. Even with a very large array, however, this time saving will be very small.
In very high performance scenarios it is possible that the slight speed advantage of the binary search method might be useful.
Lesson 4-19: Understand Excel XLOOKUP function (part two)
XLOOKUP can return more than one value
VLOOKUP is only able to return a single value.
In this example the XLOOKUP function in cell B13 is returning an array of six values because the return array has been defined as the entire range B4:G6.
XLOOKUP can search both horizontally and vertically
Legacy Excel provided both VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup) functions.
The XLOOKUP function can replace both the VLOOKUP and HLOOKUP functions as arrays can model both horizontal and vertical ranges.
In this example the lookup and return arrays are horizontal. The products shown in cells B6:B8 are searched for in cells C2:E2. When a match is found, a value is returned from cells C3:E3.
An interesting feature in this example is the use of an array (B6:B8) for the first (lookup) argument. This makes the XLOOKUP function return a spilled range into cells D6:D8.
XLOOKUP has more powerful inexact lookup features than VLOOKUP
In: Lesson 3 25: Use a VLOOKUP function for an inexact lookup, you learned about the concept of exact and inexact lookups.
Just like VLOOKUP, XLOOKUP can perform an inexact lookup but XLOOKUP has some additional features that are not available in VLOOKUP:
- There is no need to sort a table or range before using XLOOKUP.
- XLOOKUP is able to search a lookup array from last-to-first as well as first-to-last. This is useful when there are multiple matches present in the lookup array. The search direction is defined using the search_mode argument:
XLOOKUP can match using wildcards
You learned about wildcards in: Lesson 3 6: Use the SUMIF and COUNTIF functions to create conditional totals (sidebar).
Wildcards are search strings that can include the * and ? wildcard symbols.
The XLOOKUP function’s match_mode argument enables you to define a wildcard search.
This lesson is excerpted from the above book.
This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.
It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Lessons in Session 4
If you download the full e-book all of the lessons shown below (and within the lesson) can be clicked to move between lessons.
|Session Four: Working with Dynamic Arrays|
|Lesson 4‑1: Understand one-dimensional arrays|
|Lesson 4‑2: Understand two-dimensional arrays|
|Lesson 4‑3: Create a legacy CSE array formula|
|Lesson 4‑4: Understand dynamic array formulas|
|Lesson 4‑5: Understand dynamic array compatibility|
|Lesson 4‑6: Create a drop-down list using a UNIQUE function|
|Lesson 4‑7: Use a FILTER function|
|Lesson 4‑8: Create a searchable drop-down list|
|Lesson 4‑9: Use a SORT function|
|Lesson 4‑10: Use a SORTBY function|
|Lesson 4‑11: Create two linked drop-down lists using dynamic arrays|
|Lesson 4‑12: Use a FILTER function with a custom logic array|
|Lesson 4‑13: Construct Boolean AND/OR logic arrays|
|Lesson 4‑14: Use a FILTER function with AND/OR custom logic arrays|
|Lesson 4‑15: Use a SEQUENCE function|
|Lesson 4‑16: Create a perpetual calendar using the SEQUENCE function|
|Lesson 4‑17: Use a RANDARRAY function|
|Lesson 4‑18: Understand XLOOKUP (part one)|
|Lesson 4‑19: Understand XLOOKUP (part two)|
|Lesson 4‑20: Use XLOOKUP to perform a two-dimensional lookup|
|Lesson 4‑21: Use an XMATCH function|
|Lesson 4‑22: Use the SUMPRODUCT function|
|Lesson 4‑23: Use the SUMPRODUCT function with a logic array|
|Lesson 4‑24: Use a SORTBY function with a custom array|
|Session 4: Exercise|
|Session 4: Exercise Answers|
VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.
Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.
The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update. It isn’t available in older versions (Excel 2019 and earlier).
This article shows how to create an Excel VLOOKUP formula that extracts data from a different column if the first column it searches is blank.
How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.