Essential to Expert Skills progression

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.

Lesson 4-19: Understand Excel XLOOKUP function (part two)

XLOOKUP can return more than one value

understand-xlookup-part-two-illustration-1

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.

understand-xlookup-part-two-illustration-2

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:
understand-xlookup-part-two-illustration-3

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.

understand-xlookup-part-two-illustration-4
Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

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

Related Articles

VLOOKUP inexact match illustration (chicken and egg).

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.

x-lookup-tutorial-part-one

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

VLOOKUP using text strings

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.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

1 + 19 =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader