Locked lesson.

Upgrade

About this lesson

An introduction to the new XLOOKUP function in Microsoft 365. Bye, bye VLOOKUP, HLOOKUP and INDEX/MATCH!

Note: this lesson uses Excel Tables, which are introduced in the next section.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Intro to XLOOKUP.xlsx
191.8 KB Intro to XLOOKUP - Complete.xlsx
191.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

Instructions

Availability

  • The XLOOKUP function was released to Microsoft 365 AFTER Excel 2019 was released
  • This means that you must have a version of Excel newer than Excel 2019 to use it
  • Note: this lesson uses Excel Tables, which are introduced later in this course

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 Welcome to the future of LOOKUP functions XLOOKUP.
  • 00:08 This baby replaces VLOOKUP, HLOOKUP, INDEX MATCH, it does everything for you.
  • 00:13 And you think, but wait, so why did we learn about those other functions?
  • 00:18 Well, the reality is, is that,
  • 00:19 you're still going to end up running into them in the real world.
  • 00:22 And it's really important to understand how they work if you're ever going to
  • 00:25 master Excel, that's one reason.
  • 00:26 The second one is, we don't know yet whether you actually have XLOOKUP.
  • 00:31 And if you're running Excel 2019 or earlier, the answer is no you don't.
  • 00:36 So you're stuck in VLOOKUP world for a while.
  • 00:39 If you happen to be on a newer version of Excel though, or Office 365,
  • 00:43 you'll have XLOOKUP.
  • 00:44 So now you can actually start replacing your VLOOKUPs with these to make your life
  • 00:48 a little easier.
  • 00:48 Now to sort of explain why this is important.
  • 00:52 Let's take a quick look at a VLOOKUP matrix here that we're using to look up
  • 00:57 some product IDs in an items table here.
  • 00:59 Notice I've got Budweiser-15 Cans, is looking up a Lookup ID of 510007.
  • 01:04 And it's doing it in the items table, returning the second column,
  • 01:08 which is the description.
  • 01:10 And I haven't declared the final parameter,
  • 01:13 which means there's an implication here,
  • 01:15 which I'm going to actually trigger right now by doing this, 51007.
  • 01:19 Same thing, right?
  • 01:20 Except that it's not.
  • 01:21 This is a number, the previous one was actually put in this text.
  • 01:25 And when we do that, we get Perseus Cab Franc.
  • 01:27 We get a different item.
  • 01:29 Why?
  • 01:30 Well, because VLOOKUP is using an approximate match,
  • 01:34 it couldn't find the correct code.
  • 01:36 So it keeps on going through the table until it finds the closest value without
  • 01:39 going over it.
  • 01:40 In this case that's Perseus Cab Franc, not so good.
  • 01:43 This is a formula that's been used with ,FALSE at the end, and
  • 01:47 it throws up this really gross #N/A, yuck, nobody likes that.
  • 01:51 To avoid that, we actually need to go back and
  • 01:54 wrap it in another function called IFNA.
  • 01:57 This is a lot of work to trying to deal with some of VLOOKUPS issues.
  • 02:00 So, let's see if there's a better way.
  • 02:01 And there is, it's called XLOOKUP.
  • 02:05 Now I'm going to start with the very basics of XLOOKUP here, okay?
  • 02:08 We're just going to start talking straight into building a formula,
  • 02:11 not even going to talk about how this works.
  • 02:13 We're going to go pick up the LOOKUP value, okay, so
  • 02:15 what are we going to look up?
  • 02:17 And it says, where's the LOOKUP array that we're going to look in?
  • 02:19 Well, I'm going to go and pick up to look in the SKU column of the items table,
  • 02:24 fantastic.
  • 02:25 And then it says, what is the return array?
  • 02:28 Now in VLOOKUP, you have to give it a number.
  • 02:31 But inside XLOOKUP, we say, I'd like to see the description please, and
  • 02:36 we select the range of data.
  • 02:37 And now, I'll just close the parenthesis and hit Enter, and boom, it just works.
  • 02:41 So, that's one thing that's a little different.
  • 02:43 Not number of positions anymore, but actual names.
  • 02:46 Let me see if I can actually do this again.
  • 02:48 So I'm just going to drag this one down.
  • 02:50 And let's change this, instead of using item description.
  • 02:55 Is it just as easy as saying, hey, give me the Sales Price.
  • 02:58 Sure is, it's going to pick up the 29.50.
  • 03:02 Nice.
  • 03:03 Now, what if I go back and choose 510007,
  • 03:08 and break it like I did before with VLOOKUP?
  • 03:12 Notice it doesn't give me the wrong item.
  • 03:15 It defaults to an exact match unlike VLOOKUP, that is amazing.
  • 03:17 This is one of the things that I hate about VLOOKUP is it defaults
  • 03:21 to approximate.
  • 03:22 I want to know when something goes wrong.
  • 03:24 So that's cool.
  • 03:25 I'm going to press Ctrl+Z.
  • 03:26 We'll look at that a little bit more in just a second.
  • 03:29 Here's something else that's kind of interesting.
  • 03:31 Look at this one, in VLOOKUP, you must look up from the first column,
  • 03:35 it have to start in SKU, because it's the first column of the table.
  • 03:38 In this case, I want to look up Budweiser-15 Cans.
  • 03:43 I want to look that up in the second column with the description.
  • 03:47 And the one that I want to return is I want to look up the SKU number, and
  • 03:50 find the appropriate, so I actually want to look up left.
  • 03:54 Now you can do this, but you have to use index match in the old world,
  • 03:58 with VLOOKUP or with XLOOKUP rather, boom, done, nice and easy.
  • 04:02 So that's pretty awesome.
  • 04:05 All right, let's look at another really cool thing here.
  • 04:08 Let's go XLOOKUP.
  • 04:10 And this time we're going to try and look up, Sudsweiser.
  • 04:13 By the way, this doesn't exist in my table.
  • 04:17 What I'm going to do is I'm going to look up the description.
  • 04:18 I want to also call something out.
  • 04:21 You don't have to use a table for this.
  • 04:22 Notice that when I start selecting cells, it's actually giving me a range.
  • 04:26 Now, it's only when I go all the way to the bottom of the table that it actually,
  • 04:29 goes and turns this back into item description, so just be aware of that.
  • 04:33 So I'm going to look that back up and then I'm going to say,
  • 04:35 what would I like to know about it?
  • 04:37 Well, actually, I'd like to find the description for it.
  • 04:41 I'm going to look up the exact same field.
  • 04:42 So this time I'm just going to go and click on that column,
  • 04:45 because that's a little easier.
  • 04:46 And at this point, when I go back and close the parenthesis,
  • 04:49 it's going to come back with #N/A, because,
  • 04:51 again, it defaults to an exact match, not an approximate match.
  • 04:55 But I don't like that, it's kind of nasty.
  • 04:59 So why don't I do this?
  • 05:00 There's another parameter to XLOOKUP, if not found,
  • 05:06 I don't say quote, Product not found, Enter.
  • 05:12 And now, beautiful, that just works nicely.
  • 05:14 If I go back and say, give me Budweiser, it gives me Budweiser,
  • 05:19 if it's not found, it gives me a nice message to that.
  • 05:22 This gets even more interesting though,
  • 05:25 when we actually start playing around with approximate match scenarios.
  • 05:30 Because you think well, yeah, but I do like VLOOKUP ability to do that, sure,
  • 05:35 me too.
  • 05:35 But when I want it, by default, we go to an Exact match.
  • 05:39 But you'll notice that we also have the ability to choose a match mode.
  • 05:43 0-Exact match is the default, VLOOKUP replicates something similar to this,
  • 05:49 exact match or next smaller item.
  • 05:51 So let's try this, -1.
  • 05:55 And it comes back with Stella.
  • 05:57 Okay, cool.
  • 05:59 Why don't we try this the other way though,
  • 06:00 because we also have another option.
  • 06:02 So I'll just pull this one down.
  • 06:03 And instead of using -1, which is the Exact match or
  • 06:07 next smaller, we're going to go Exact match or next larger item.
  • 06:12 This is impossible with VLOOKUP.
  • 06:15 When I hit Enter, I get Sumac Ridge.
  • 06:17 What this is telling me, I can't find Sudsweiser.
  • 06:21 So I'm going to go and look through your items, but
  • 06:22 there's something else here that's critically important to be aware of.
  • 06:25 Let me just filter this for a second, Text Filter begins with s.
  • 06:32 What you can see here is that Stella is the first item in the list and
  • 06:36 Sumac is the last item in the list.
  • 06:39 Okay, fine.
  • 06:40 So you think, all right, so it's looking from the top of the table to the bottom,
  • 06:43 but there's something important that you need to be aware of here.
  • 06:45 This data, if I put in another s, comes back with Ssudsweisers.
  • 06:51 Now, it gives me Smirnoff Twist Raspberry.
  • 06:54 What I want you to recognize here is that the first item looking in this list is
  • 06:58 actually Stella.
  • 06:59 But it comes up in last position.
  • 07:01 Unlike VLOOKUP, this data does not need to be sorted because XLOOKUP
  • 07:06 does the sorting for you automatically.
  • 07:09 It uses a different algorithm what VLOOKUP uses, in order to actually allow
  • 07:13 you to do this information without having to sort all of your multiple columns,
  • 07:18 because let's be honest, that's impossible.
  • 07:20 And there's a lot more about XLOOKUP, tons more.
  • 07:23 But this is the real basics.
  • 07:24 It's going to allow you to replicate things a lot faster and
  • 07:29 actually build some pretty cool stuff.
  • 07:32 I should also mention, it's actually faster than VLOOKUP too,
  • 07:37 so some pretty cool things with XLOOKUP.

Lesson notes are only available for subscribers.

Gift this course