Locked lesson.
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.xlsx32.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
- 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.