This is the second of a two-part series of XLOOKUP lessons. If you haven’t already done so I recommend that you begin with part one by clicking this link.
- Excel 365
- Excel 2019 and earlier
- Last Updated: September 19, 2020
note
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.
Covered in Session 12 – Power Pivot, Data Modelling, OLAP and Business Intelligence
Covered in Lesson 12-20: Use the CUBEVALUE function to query a data model.
Covered in Session 13: An Introduction to DAX
Covered in Lesson 12-6: Use an OLAP pivot table to analysze data residing in a data model.
Covered in Lesson 11-33 Create a merged query using fuzzy logic.
Covered in Session 10: 3D Maps.
Covered in Lesson 11-7: Create and use a custom data type.
Covered in Lesson 9-2: Use Natural Language Queries.
Covered in Lesson 11-8: Use custom data types in formulas.
Covered in Lesson 9-1: Use automatic data analysis to create data insights.
Related Articles
VLOOKUP inexact match
VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.
VLOOKUP exact match
Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.
Excel XLOOKUP function (part one)
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).
Using VLOOKUP, if Column 1 is blank, get value from Column 2
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.
Use the SUMIF and COUNTIF functions to create conditional totals
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.
VLOOKUP using text strings
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.
