 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2019, 2016 and Mac versions more…

# Excel XLOOKUP function (part two) 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.

# 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.

## 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