Locked lesson.

Upgrade

About this lesson

Advanced options for XLOOKUP including using wildcard matches and returning Dynamic Arrays as an output.

Quick reference

XLOOKUP and Dynamic Arrays

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

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 In this video, I'd like to look at XLOOKUP again but this time we're going to look at
  • 00:10 the impact of dynamic arrays on XLOOKUPs as well as mix in wildcard matches.
  • 00:15 So we're going to start first by doing a basic XLOOKUP where we will
  • 00:20 do equals XLOOKUP.
  • 00:22 We'll choose which value we're going to look up, we'll choose where we're going
  • 00:26 to find it so this is going to be in the description column of items table.
  • 00:30 But now I'm going to start playing with the dynamic arrays aspect because what I
  • 00:35 want to return here is not any specific column, I want to return all of them.
  • 00:40 So I'm going to type in the name of the table here.
  • 00:43 And at this point in time what I'm going to do is I'm going to come back and
  • 00:46 I should probably also put in a nice little message here about product not
  • 00:51 found just in case something goes crazy wrong.
  • 00:53 We'll close our parenthesis, hit Enter and what you'll notice is that
  • 00:58 the dynamic array brings back the entire row for the table.
  • 01:02 So that is pretty darn cool.
  • 01:06 Now, what I want to do next is I want to copy this formula down and
  • 01:10 I'm going to adjust something in it.
  • 01:12 Instead of bringing back the entire row from the table,
  • 01:16 I'm going to bring back certain columns only.
  • 01:18 To do that what I'm going to do is I'm going to find my items table here,
  • 01:22 there we are.
  • 01:23 And instead of grabbing the entire table, what I'm going to do is I'm going to grab
  • 01:28 the sales price through the margin columns.
  • 01:30 And you can see when you take a look in the formula bar why I didn't want to type
  • 01:34 this because it's got a kind of a weird syntax with double braces around different
  • 01:38 things, colons and stuff like that.
  • 01:40 But once I've selected those we can see we're looking up in the description column
  • 01:44 and we're going to bring back the sales price, unit price or cost and
  • 01:48 margin columns and as I hope you expect, when I hit Enter, what do I get?
  • 01:52 I get those values.
  • 01:55 Now, obviously this isn't structured consistently with what I actually have in
  • 02:00 my table so far so that's a challenge but at least it's doing what I told it to.
  • 02:04 Now, let me copy this one down here and see what happens next.
  • 02:10 Well I get product not found.
  • 02:12 Why?
  • 02:13 Well, because I'm looking up Sudsweiser not Budweiser.
  • 02:17 And I don't have a Sudsweiser in my table so
  • 02:20 this is going to return me a product not found.
  • 02:23 But what I want to know is can I do better than that?
  • 02:28 So let's go into our next cell here and let's go and look for
  • 02:33 *Spring* and see what we get back this time around, okay?
  • 02:39 So I'm going to go with equals XLOOKUP, there we are.
  • 02:43 We're going to look up *Spring*, where are we going to look it up,
  • 02:47 that's going to be in our description column.
  • 02:50 And which array are we going to return?
  • 02:52 I'm going to return the entire items table.
  • 02:54 And once again, if we don't find anything,
  • 02:57 I'll return a nice little message about product not found.
  • 03:02 And close my parenthesis and right now I'm going to go and hit Enter.
  • 03:07 Now, it comes back with product not found because it didn't find anything obviously.
  • 03:11 So let's go back and just modify this for a second.
  • 03:14 For the final parameter here we have a match mode which is exact match
  • 03:19 next smaller, next larger.
  • 03:21 So what I'm going to do is I'm just going to change this to next larger item
  • 03:24 and see what we get in this case.
  • 03:26 And you'll notice that it comes back with product 5700271
  • 03:31 which is the Bird In Hand Cab Sauvignon.
  • 03:34 All right, well that's kind of interesting.
  • 03:35 Why that one?
  • 03:37 Well, if we go and we sort our entire data table here, what you'll notice
  • 03:43 is that Bird in Hand is the very first record, stars come before A.
  • 03:47 And this is why the next larger item returns this particular product but
  • 03:52 that's not exactly what I want.
  • 03:54 Let's try something different.
  • 03:56 I'm going to copy this down and
  • 03:58 I'm going to make one small modification to this formula.
  • 04:01 At the very end here, what I'm going to do is I'm going to change the match mode
  • 04:06 to wildcard character match and we're going to Enter.
  • 04:10 And what do I get?
  • 04:11 I get Ok Springs.
  • 04:13 Notice we were looking for *Spring*, we got Ok Springs.
  • 04:18 Why?
  • 04:18 Because Spring is in the middle of it.
  • 04:20 So what's going on with this one here?
  • 04:22 Well, the star when you turn on wildcard mode represents a wildcard character.
  • 04:30 And what that means is any number of characters before this text,
  • 04:34 any number of characters after this text, okay?
  • 04:38 So the star represents any number of characters.
  • 04:41 That's different than what we see here with four question marks.
  • 04:46 Let's go and take a look at this.
  • 04:48 We get back Spring IPA.
  • 04:52 Now, there's a couple things here that are really important to recognize.
  • 04:55 Number one, Spring IPA doesn't have anything before it and yet
  • 04:59 the star that we had in our pattern that we were searching for is before spring.
  • 05:04 So that wildcard number of characters with an asterisks can represent
  • 05:09 any number of characters from none up to however many there.
  • 05:13 Then there's the actual textual word spring and
  • 05:16 after that we have four question marks.
  • 05:19 How many characters are after spring in Spring IPA?
  • 05:22 There's four space IPA.
  • 05:24 And if I were to go back here and say F2 and drop one of these off,
  • 05:29 notice the product is not found.
  • 05:32 The question mark refers to a specific number of characters.
  • 05:37 So that's one character, I have for question marks, it has to match for
  • 05:41 individual characters.
  • 05:42 So using this you can actually be really,
  • 05:45 really careful with the way that you actually build things up.
  • 05:48 The last thing I'm going to do is I'm going to take this, I'm going to go and
  • 05:51 say, all right, let's go and bring this down, we've got our Ok Springs but
  • 05:55 I'm going to change the sorting order or the searching order of this one as well.
  • 05:59 So in this particular case, we're searching most of the time by default from
  • 06:03 the top to the bottom of the list.
  • 06:05 What I'm going to do at this point is I'm going to go and search from last to first
  • 06:10 and we're going to see if this actually gives us any difference in our result and
  • 06:14 you can see that it absolutely does because there is a Spring XPA as well.
  • 06:19 So there you go, that one starts looking from the bottom of the table up
  • 06:23 while the default orders start looking from the top of the table down.
  • 06:27 So there's some really cool functionality in XLOOKUP here particularly when you
  • 06:31 combine it with dynamic arrays and all of these awesome extra little pieces that go
  • 06:36 in there that make it just so much more robust than VLOOKUP.

Lesson notes are only available for subscribers.

Gift this course