How To Use The New
 XLOOKUP Function + Examples

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Microsoft has just announced the new ‘XLOOKUP’ function in Excel. 

Rumor has it that this new function is more powerful and robust than ‘VLOOKUP’ and ‘HLOOKUP’. Some say that this function is an ‘INDEX’ + ‘MATCH’ killer.

But how does ‘XLOOKUP’ really stand against these functions?

In this article, we’ll explore the new ‘XLOOKUP’ function as well as teach you how to use it.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get into it! 🔍

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Introduction to XLOOKUP

The ‘XLOOKUP’ function is the successor to the old functions ‘VLOOKUP’ and ‘HLOOKUP’.

Like its predecessors, ‘XLOOKUP’ performs lookups

But this time, it can go vertical or horizontal.

Kasper Langmann, Co-founder of Spreadsheeto

Some of its advantages against the older functions include:

  • Returning results from more than 1 column
  • Default match type is exact (that means, you can leave it blank if you’re searching for a specific value)
  • Can perform a lookup to the right or left of the lookup value
  • Capability to return a range instead of just a single value
  • As stated, can perform lookup horizontally or vertically
  • Can lookup data in reverse order
  • Deals with arrays natively

Handy, right? 😎

Here’s the caveat:

As of now, ‘XLOOKUP’ is currently a beta feature and is only available for Office Insiders at this time. But don’t worry, it’s expected to be released to all subscribers once the function is ready.

The XLOOKUP Syntax

This function’s syntax is easy to understand. It’s a bit similar to ‘HLOOKUP’ and ‘VLOOKUP’ functions’ syntax.

The ‘XLOOKUP’ syntax:

=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])

Parameters:

  • ‘lookup_value’ – the value to look for
  • ‘lookup_array’ – the range or array to search for the value
  • ‘return_array’ – the range or array to return
  • ‘match_mode’ – optional; represents the match type; default is an exact match
  • ‘search_mode’ – optional; represents the search behavior; default is “search from first value”

The ‘match_mode’ argument has 4 types:

  • 0 – default; exact match; return error (#N/A) if no match
  • -1 – exact match or next smaller value
  • 1 – exact match or next larger value
  • 2 wildcard match

The ‘search_mode’ argument also has 4 modes:

  • 1: default; search from the first value
  • -1: search from last value
  • 2: ascending binary search values
  • -2: descending binary search values

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to use the ‘XLOOKUP’ function

Using the ‘XLOOKUP’ function is very easy.

Unlike the ‘VLOOKUP’ and ‘HLOOKUP’ functions, there’s no need to specify the column number to return.

To illustrate further, let’s use this dataset as an example:

dataset for xlookup exercise

Let’s say you would like to use the ID as the lookup value and return all the available data about that ID.

For this purpose, let’s use “505”. 

Now, all you have to do is supply the parameters with the appropriate cell references:

  • ‘lookup_value’ – C3
  • ‘lookup_array’ – C7:C16
  • ‘return_array’ – D7:G16

Write the formula on the cell where you would like to display the results. In this case, the result starts at cell D3:

=XLOOKUP(C3,C7:C16,D7:G16)

Pretty easy, right? 😊

Return a single result using XLOOKUP

If you would only like to return a single value, let’s say the last name, all you have to do is tweak the formula.

With this, you’re able to beat the ‘INDEX’ + ‘MATCH’ combo without breaking a sweat! Adding the optional arguments, ‘match_mode’ and ‘search_mode’, will make it exactly like the ‘INDEX’ + ‘MATCH’ combo but in 1 function!

Kasper Langmann, Co-founder of Spreadsheeto

Specifically, the ‘return_array’ parameter.

Instead of referring all the columns (D7:G16), just put the column where the return array is located.

The formula then written on D3 becomes:

=XLOOKUP(C3,C7:C16,D7:D16)

looking up the last name of ID 505 using xlookup

Wrapping things up…

With the ‘XLOOKUP’ function, there’s no need to complicate things using the older lookup functions. As you can see, using this function is as easy as supplying the appropriate references.

No hard thinking necessary! 😊

However, you might have to wait a bit to enjoy this function. But rest assured, it will be worth the wait! 👍

Kasper Langmann, Co-founder of Spreadsheeto
2019-10-16T13:50:24+00:00