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, 2021, 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

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

Which tutorial do you need?