Wildcard XLOOKUPs and Dynamic Arrays

Locked lesson.

Upgrade

About this lesson

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

Exercise files

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

Wildcard XLOOKUPs and DAs.xlsx
29.3 KB Wildcard XLOOKUPs and DAs - Complete.xlsx
29.6 KB

Quick reference

Wildcard XLOOKUPs 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 All right, are you ready for some mind-blowing awesomeness?
  • 00:07 Because what I'm about to show you here can absolutely not be done with VLOOKUP,
  • 00:12 or XLOOKUP, or INDEX-MATCH.
  • 00:14 This stuff here is incredible.
  • 00:16 Now, first thing I'm going to do is I'm just going to give myself a little
  • 00:19 bit more space so that we can actually see the data in our table here.
  • 00:22 And what I want to pull off is some magic here,
  • 00:25 where I'm combining XLOOKUP and dynamic arrays together.
  • 00:29 And this is kind of neat, because here's what's going to happen.
  • 00:33 We're going to go XLOOKUP, and we're going to start by looking up Budweiser.
  • 00:37 Where are we going to look it up?
  • 00:38 We'll look it up in the description column of our items table.
  • 00:42 So that's easy so far.
  • 00:44 But then it says, okay, what would you actually like to return?
  • 00:47 And you'll notice that the return actually doesn't say return column or
  • 00:51 return number, it says return array.
  • 00:53 And an array can actually be a single column, single row, or
  • 00:57 multiple columns and rows all together.
  • 00:59 In this case, multiple columns and rows can come from the items table or
  • 01:05 from a range, A5 to D92, for example.
  • 01:08 And at this point, I could go back and say, you know what, let's go also and
  • 01:13 put something on and say, hey, look, if it's not found, we can say,
  • 01:17 product not found, because why not?
  • 01:19 Close our parenthesis, and we'll hit Enter.
  • 01:22 Boom, look at that.
  • 01:24 Not only do we get the actual skew that was picked up,
  • 01:27 because we said we want the entire row.
  • 01:30 But we get the skew, the product, the category, the price,
  • 01:34 the unit cost, and the margin.
  • 01:35 How cool is that?
  • 01:37 You think, yeah, okay, cool, but you know what, I don't actually want all of that.
  • 01:41 All I care about is the sales price, unit cost, and
  • 01:43 margin because I know what product it is.
  • 01:45 Why not? So I can do that.
  • 01:46 Let's go XLOOKUP, we'll look up again our Budweiser 15 can.
  • 01:51 We'll look it up in the description because that's where we're going to
  • 01:54 find that particular reference.
  • 01:56 And then for the return array, now, you can type this, but
  • 01:59 I find this way easier to just go and select.
  • 02:01 So we'll let our cursor come into this little black arrow, left-click and drag.
  • 02:06 And that puts this little setup in here for working with columns of a table.
  • 02:10 So it's item, square brace, and then square brace, column name, colon,
  • 02:14 square brace, column name, close that square brace again.
  • 02:17 Once we have that, of course, we'll go product not found,
  • 02:22 just in case we can't actually find it.
  • 02:25 At that point, we now get just those items.
  • 02:29 So that's kind of cool.
  • 02:30 Now, at this point, we should verify that that actually worked.
  • 02:34 Let's just pull this one down.
  • 02:35 Notice, product not found because we couldn't find Sudweisser.
  • 02:41 But if we go equals and pick up the one above,
  • 02:44 it dynamically pulls the correct values.
  • 02:46 It doesn't dynamically do formatting.
  • 02:48 I've actually preformatted those cells and didn't preformat these ones, and
  • 02:51 that's why the numbers actually look different there.
  • 02:53 So you gotta keep that in mind as you're doing this as well.
  • 02:56 But I'll go back to product not found for right now.
  • 02:59 Now, here's what I'd like to do at this point.
  • 03:02 I'd like to actually start playing around with these funky little things here,
  • 03:05 like star, Spring, star.
  • 03:06 What is that all about?
  • 03:08 Let's go and do an XLOOKUP on star, Spring, star.
  • 03:15 And what we're going to do is we're going to pick up the lookup array,
  • 03:18 we'll look it up in the description column.
  • 03:20 And we're going to return the name of the table, we'll grab all columns.
  • 03:23 If it's not found, we're going to go product not found again.
  • 03:29 And at this point, I'm just going to go and say, all right, cool.
  • 03:34 Let's go and say, for a match mode, if we can't find it,
  • 03:37 we'll go with the exact match or next larger item, so we'll go with the one.
  • 03:42 And we'll go and say, okay, and Enter.
  • 03:45 And it comes back, when looking for Spring, it says okay,
  • 03:50 that must be the Bird in Hand Cab Sauvignon.
  • 03:53 Why?
  • 03:54 Well, the answer is that the asterisk character comes before anything else
  • 03:59 in the alphabet.
  • 04:00 So the first thing that it finds is the next larger item is Bird in Hand because
  • 04:04 there's no products in my list that start with A.
  • 04:06 So that's the very first item.
  • 04:08 So that's interesting, but it's not actually what I'm really interested in.
  • 04:12 What I'm trying to do is actually use a wildcard match to find something,
  • 04:18 give me the first item or second item that has the word spring within it.
  • 04:24 So check this out.
  • 04:25 We're going to go and we're just going to drag this down a little bit more,
  • 04:28 we'll replicate that formula.
  • 04:29 But we're going to change the final match mode here.
  • 04:33 So I'll just back up and put in the comma again so you can see the options,
  • 04:37 because the very last one is wildcard character match.
  • 04:40 When I put this in and hit Enter, you'll notice that it comes back with Ok Springs.
  • 04:45 This is the first item that contains the word springs.
  • 04:50 And what the star does it says any number of characters,
  • 04:53 from zero up to however many are in the text, before the word spring, and
  • 04:58 any number of characters after the word spring, from zero to however many.
  • 05:02 That's the match that I'm actually looking for, so can you pick that up for me?
  • 05:06 And that's exactly what it does.
  • 05:08 So that's pretty cool.
  • 05:09 But now what I'd like to do is I'd like to see if I can find something else.
  • 05:13 So I'm going to go grab this one here, drag it down again.
  • 05:17 And I'm going to change, notice, the pattern to this, star, Spring,
  • 05:21 question mark, question mark, question mark, question mark.
  • 05:24 So what's that all about?
  • 05:26 The asterisk says any number of characters,
  • 05:29 from zero to however many you find, before the word.
  • 05:32 So it says anything that comes before the word spring I'll except,
  • 05:36 and then it has to have the word spring.
  • 05:38 And then the question mark says there must be a character for this space.
  • 05:43 So in this case, something that is four characters long.
  • 05:46 And you say okay, well, but hang on, Spring IPA, that's only three characters.
  • 05:50 No, it's not.
  • 05:51 There's a space in there and that counts.
  • 05:53 So in this case, we're looking very specifically for
  • 05:56 a word that has maybe something in front of spring, it may not,
  • 06:00 but it has to have the word spring followed by four characters.
  • 06:04 So if I were to go down and change Spring IPA to Spring IPAs,
  • 06:09 that's now five characters afterwards.
  • 06:12 And it comes back and says, I'm sorry, that doesn't match anymore, but
  • 06:17 this Spring XPA, that one does.
  • 06:19 So it'll bring that one back instead.
  • 06:21 Let me just undo that, though, for a sec.
  • 06:23 Because I also want to show you that we have one more really cool option here too.
  • 06:27 Let me drag this one down again.
  • 06:29 In this case, I'm getting Ok Springs 1516.
  • 06:34 This is the same match as what we've actually used here.
  • 06:37 Spring, we're looking in the description, returning all columns,
  • 06:41 using the wildcard match.
  • 06:42 But we're searching from the first to the end of the data set.
  • 06:45 You'll notice here I have an option to do the opposite.
  • 06:48 Let me look from the bottom of the data set up.
  • 06:51 So let me come here, and what we'll do is we'll come down and
  • 06:54 we're now going to use the final parameter of XLOOKUP, which is called search mode.
  • 06:59 Notice that search mode, search first to last, number one, is actually default.
  • 07:03 If you don't declare it, that's what it uses.
  • 07:05 But we have the option to go minus one.
  • 07:08 Now, the other guys, two and minus two,
  • 07:10 those are actually the way that VLOOKUP really does this approximate match search.
  • 07:14 So if you absolutely have to have VLOOKUP's binary search method,
  • 07:17 which returns really weird results if the data is not sorted, you got the option.
  • 07:22 As a matter of fact,
  • 07:22 you can even do it in reverse searching from the bottom of the table too.
  • 07:25 But honestly I don't really find a lot of people that really want that one.
  • 07:29 So let's go with minus one, and check this out.
  • 07:31 When we hit that,
  • 07:32 it comes back now and says, find me the last word with spring in it.
  • 07:37 Looking from the bottom of the table up is Spring XPA.
  • 07:40 So that's kind of neat, because if we go and take a look at all the springs in
  • 07:45 the table, there are some Ok Springs, Ok Springs Pale Ale, Spring IPA, XPA.
  • 07:50 This is the last guy in alphabetical order, and
  • 07:52 that's why it picks it up there.
  • 07:53 So there's some cool ways to actually integrate your XLOOKUPs with
  • 07:57 dynamic arrays, as well as execute wildcard matching and
  • 08:01 searching from bottom to top as well.
  • 08:03 Some really cool flexibility inside of this.
  • 08:06 The other thing you should keep in mind here, you don't have to go up and
  • 08:09 down the table like we've been doing.
  • 08:11 You can also go horizontally as well if you decide to craft your XLOOKUPs
  • 08:14 that way.
  • 08:15 So it can actually replicate HLOOKUP just as well as VLOOKUP.

Lesson notes are only available for subscribers.

Gift this course