Locked lesson.

Upgrade

About this lesson

An introduction to the new XLOOKUP function. Bye, bye other lookups!

Exercise files

Download this lesson’s related exercise files.

Intro to XLOOKUP - Begin.xlsx
32.4 KB Intro to XLOOKUP - Complete.xlsx
32.4 KB

Quick reference

Introduction to XLOOKUP

An introduction to the ultimate lookup function: XLOOKUP

When to use

When you need to do a VLOOKUP, HLOOKUP, or INDEX/MATCH and have a modern version of Excel.

NOTE:  This is a complete guide to XLOOKUP and contains concepts that have not yet been illustrated in the course.

Instructions

Availability

  • The XLOOKUP function was released to Office 365 AFTER Excel 2019 was released
  • This means that you must have a version of Excel newer than Excel 2019 to use it

Advantages over classic lookup formulas

  • Defaults to an Exact match (unlike VLOOKUP)
  • Data does not need to be sorted (unlike VLOOKUP)
  • Data being searched does not need to be in the first column (no need to resort to Index/Match)
  • Optimized for performance
  • Contains built-in options for alternate results
  • Does not rely on hard-coded column positions for matches
  • Provides robust match and search modes
  • Provides wildcard lookups
  • Is compatible with Dynamic Arrays, meaning one lookup can return multiple columns or rows of data

XLOOKUP Syntax

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

XLOOKUP Parameters

  • lookup_value:
    • The value (or cell reference) you wish to look for
  • lookup_array:
    • The range you wish to look in to find a match
    • Can be a range, table row, or table column
    • Must be a single column or row
  • return_array
    • The range (or table column/row) you wish to return when a match is found
    • To return a single value, provide a range that covers over one column (or row)
    • To return multiple values, provide a multi-column (multi-row) range
    • To return all columns in case of a match, provide the address of the entire data range or table name
  • if_not_found
    • Optional, but defaults to #N/A if a match isn’t found
    • To provide a text output, wrap it in quotes:  “Product not found!”
    • To provide a numeric output, it is not necessary to wrap the output in quotes
  • match_mode
    • Optional, but defaults to an Exact match, unlike VLOOKUP
    • 0              Returns an Exact match (or #N/A unless overridden with the “if_not_found” parameter
    • -1            Exact match or next smaller item (like VLOOKUP’s approximate match)
    • 1              Exact match or next larger item (not possible with VLOOKUP)
    • 2              Wildcard match
  • search_mode
    • Optional, but defaults to search from first to last
    • 1              Sorts your data and searches from first item to last until it finds a match
    • -1            Sorts your data and searches from last item to first until it finds a match
    • 2              Binary search – ascending – searches using VLOOKUP’s sort methods
    • -2            Binary search – descending – searches using VLOOKUP’s sort methods, but in reverse order
Login to download
  • 00:04 All right, we've spent a lot of time looking at a variety of lookup functions,
  • 00:09 and now I want to introduce you to the lookup function that we're going to
  • 00:14 use if you are on Excel 2021 or higher or on Office 365.
  • 00:17 Let me introduce you to XLOOKUP.
  • 00:20 This is the replacement for VLOOKUP, HLOOKUP, index match,
  • 00:24 all those good things, and it's amazing.
  • 00:26 So, here's the deal though, before we dive in, we have a table of product SKUs and
  • 00:31 descriptions, so those are stock keeping units, and here we have a few different
  • 00:36 VLOOKUP functions that are looking up some key stats.
  • 00:39 What I want you to recognize, there is a VLOOKUP that's in the middle here that is
  • 00:43 returning a specifically numbered column from our table.
  • 00:46 And it is wrapped in an IF N/A function just in case we come back with a bad
  • 00:50 match to give us product not found.
  • 00:52 It's ugly and it's complicated looking, and it scares people.
  • 00:56 So, let's see if we can do better with XLOOKUP.
  • 00:59 The first thing that I'm going to do is I'm going to start over here, and
  • 01:03 we're going to look up SKU number 510007 just like the VLOOKUP is doing.
  • 01:08 And we're going to start by typing in XLOOKUP.
  • 01:10 You'll notice that XLOOKUP asks us for a lookup value, just like VLOOKUP.
  • 01:15 So, what is it we're looking up?
  • 01:17 We'll look up the value in J7.
  • 01:19 Where are we going to look?
  • 01:20 Now, in this case, what we do with XLOOKUP is we actually pick up just the specific
  • 01:25 column that we need.
  • 01:26 I'm going to also mark this down with F4 to lock this in so
  • 01:30 that I can actually copy this formula little bit later on.
  • 01:34 Now, that's going to give us the column we're looking in.
  • 01:38 If we find something, we want to return a match in an offsetting column, and
  • 01:42 unlike VLOOKUP and the other lookups where we give a numeric index,
  • 01:46 what we do is we select the range of data that we're actually looking for.
  • 01:51 So, I'm going to go and lock this in with an F4 as well, the important thing here,
  • 01:56 these ranges need to have the same number of rows, that's the big piece here.
  • 02:01 What I'm going to do now is I'm just going to close my parenthesis.
  • 02:05 I'm going to hit Enter and it gives me Budweiser 15 cans, and
  • 02:09 you think okay, cool.
  • 02:11 But what happens if I go back now and put a five on this,
  • 02:15 and this product does not exist.
  • 02:17 Notice that unlike VLOOKUP, which defaults to an approximate match,
  • 02:22 XLOOKUP defaults to an exact match, which is usually what you actually want.
  • 02:28 So, that's pretty cool.
  • 02:29 But obviously this is pretty gross, we don't want the N/A.
  • 02:32 So, do I have to wrap it in an IF N/A statement?
  • 02:35 And the answer is, I do not.
  • 02:37 So, if I go and hit F2 on this, I'm going to back up behind the parenthesis here and
  • 02:42 you'll notice that after return array, there's an optional if not found.
  • 02:46 Let me hit my comma here.
  • 02:48 I'm going to put in quotes.
  • 02:49 I'm going to say product not found, and close the quotes and hit enter.
  • 02:56 And it now tells me my product is not found.
  • 02:58 So, there's no additional function needed for this, it's built right in.
  • 03:01 Let me go back and set this back up though.
  • 03:03 Let me get rid of that five.
  • 03:05 There we are, Budweiser 15 cans.
  • 03:07 Fantastic, that is brilliant.
  • 03:09 A much easier function to write that is actually safe for your N/A values.
  • 03:15 Now, let me show you something else though that's kind of cool.
  • 03:18 I'm going to copy this one down, so we're still pointing to 51007.
  • 03:22 This time, I've got the SKU, what I want is I want the sales price returned.
  • 03:27 So, I'm going to come back and I'm going to modify the formula instead of going
  • 03:31 to Column C, what I'm going to do is I'm going to go and
  • 03:35 grab the sales price column over here, which is column.
  • 03:38 What are we here?
  • 03:39 We're in E.
  • 03:40 Let me just mark that down, F4.
  • 03:43 Bingo, there we go.
  • 03:44 51007, just like VLOOKUP is returning gives us 2950, awesome.
  • 03:50 Now, let me show you something that VLOOKUP cannot do.
  • 03:54 I'm going to copy this one down.
  • 03:56 The thing is I want to look up Budweiser 15 cans, but
  • 03:59 I don't want to look it up in the SKU column.
  • 04:02 I want to look up Budweiser in the description column.
  • 04:06 So, here's what's going to happen.
  • 04:07 We're going to say, look up J9.
  • 04:10 But instead of looking it up in SKU, we're going to look it up in description,
  • 04:14 let me just lock that in with an F4.
  • 04:16 And what do I want to return?
  • 04:18 I actually want to return the SKU column, so
  • 04:21 I'm going to replace this purple reference here instead of sales price,
  • 04:25 we're going to replace it with SKU and mark this in.
  • 04:28 I'm going to hit Enter, and what you can see now is that Budweiser returns 510007.
  • 04:34 This is amazing.
  • 04:36 Why?
  • 04:37 Because VLOOKUP has to look in the first column of the table and
  • 04:41 look off to the right.
  • 04:42 In VLOOKUP, you cannot look in the second column of the table and
  • 04:47 look left, but XLOOKUP can, that's awesome.
  • 04:50 Now, let me do a little bit more here, I'm going to drag this one down and
  • 04:54 we're going to look up Sudsweiser, okay?
  • 04:56 Sudsweiser does not exist in this table, all right?
  • 04:59 That's the first thing I'm going to tell you,
  • 05:01 that's why we're getting product not found.
  • 05:02 I'm going to adjust the formula just a little bit here though because I don't
  • 05:06 want to return the SKU.
  • 05:07 What I actually want to return this time is I'm going to look up the name and
  • 05:10 return the name.
  • 05:11 So, I'm basically just trying to see if the product exists, all right?
  • 05:14 So, here we go.
  • 05:15 We get product not found.
  • 05:18 And you would expect that because Sudsweiser doesn't exist in this table,
  • 05:21 but I want to show you something really, really cool here.
  • 05:24 If I drag this one down,
  • 05:27 I have an additional parameter for match mode, Common.
  • 05:32 Now, notice we've got lots of different options here and I'm only
  • 05:35 going to show you a couple of these ones, we've already seen exact match.
  • 05:38 What I'm going to grab right now is I'm going to grab minus one, exact match, or
  • 05:42 next smaller item.
  • 05:43 So here we go, minus one, and Enter.
  • 05:47 And it gives me Stella Artois can.
  • 05:50 Okay, this is interesting.
  • 05:52 Let me do one more here.
  • 05:53 I'm going to change this one now to use positive one for our match mode.
  • 05:58 And this will give me the next largest item, and
  • 06:01 it gives me Sumac Ridge Gilbert's.
  • 06:03 Now, I've highlighted both of these in the table.
  • 06:05 And there's two important observations that I want you to make about this.
  • 06:09 Number one, the data in this column, this is our lookup column, is not sorted.
  • 06:16 This is important to realize because what's happened here is we said, look up
  • 06:21 the description and if you can't find Sudweiser, give me the next smaller item.
  • 06:26 And it came back with Stella Artois six can, and
  • 06:29 that is the T comes before U in the alphabet.
  • 06:32 So, this makes sense, it's the earlier item, even though the data is not sorted.
  • 06:35 That's awesome.
  • 06:36 Meanwhile, in the next line, I said give me the next larger item, and
  • 06:41 that's why it picked up Sumac Ridge, because Sumac comes after Suds.
  • 06:45 So, this is pretty cool.
  • 06:46 Now, this isn't all the options for XLOOKUP.
  • 06:48 But I think what you're going to find here is this is much easier to actually work
  • 06:53 with, much more robust and allows you to do a lot more things,
  • 06:56 XLOOKUP is very exciting.

Lesson notes are only available for subscribers.

Gift this course