Locked lesson.
About this lesson
In this lesson, we learn the basic uses of XLOOKUP by building your own invoice template.
Quick reference
XLOOKUP Function
Learn the basic uses of XLOOKUP, available only in Microsoft 365.
When to use
This new function was created to replace the VLOOKUP and INDEX & MATCH as it combines the best of both and adds an extra kick to the mix.
It is used to lookup a value in one dataset to find a similar value in another set and return a related value.
Instructions
- XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode])
- XLOOKUP(What, where, what to return, [special message], [exact or approximate], [search mode])
- [ ] means the argument is optional, remember the default match mode is EXACT
- The lookup array and the return array is split where the VLOOKUP has it in one table – this frees the XLOOKUP to find a value left or right of the lookup array
- Note the use of $$ to make copying the formula more effective
- 00:04 So once again, here is our example invoice.
- 00:07 In this invoice I'd like to be able to fill in a client code,
- 00:13 an item code, and a quantity.
- 00:16 And then I would love for Excel to complete the rest of the invoice for me.
- 00:20 Details about the item code are available in an inventory list on the right.
- 00:25 Details about the client code are available in a client list above
- 00:29 the inventory.
- 00:31 So how can I grab the information I need from the inventory and client lists,
- 00:36 and put them into the corresponding spots in the invoice?
- 00:39 I would need a lookup function.
- 00:42 And In this example, we're going to use xlookup.
- 00:46 Xlookup is a new function that improves upon the older vlookup and
- 00:50 hlookup functions.
- 00:52 It's structured a little differently, so
- 00:55 it overcomes some of the limitations of vlookup and hlookup.
- 00:58 You'll see what I mean.
- 01:00 Let's try it out.
- 01:01 So we type in xlookup, and the first argument, is lookup value.
- 01:06 This is the value you want to use to find a corresponding value in another list,
- 01:11 that'll be our item code here.
- 01:14 The lookup array is the area in the list that this lookup value can be found in.
- 01:20 The return array is the area in the list that you want returned.
- 01:26 So, there are three other optional arguments.
- 01:29 For details on what exactly those entail, you can look at our other xlookup videos,
- 01:34 but it's good to know that the default value for
- 01:37 matching in xlookup is an exact match.
- 01:40 So, if we type in nothing else for this formula, we're looking for
- 01:43 an exact match on our lookup value.
- 01:45 So when we hit enter, we get electric guitar.
- 01:50 Let's demonstrate xlookup again, in the price field.
- 01:53 Xlookup, and the lookup value is our item code.
- 01:58 The lookup array where I will find this lookup value is our column of item codes.
- 02:04 The return array I want to return, which is our prices in this example.
- 02:10 And if I leave out my optional arguments, once again,
- 02:13 default xlookup match is exact match.
- 02:16 So we hit Enter, and I have my price.
- 02:20 Cool, so let's look at the client code now.
- 02:24 We could do another xlookup here.
- 02:27 Our lookup value is in the cell above H7 where our client code is.
- 02:31 Where to find that, well, my lookup array is this row of client codes from the list.
- 02:37 If it finds a lookup value, what do we want returned?
- 02:40 It's a client name, which is in the row just below.
- 02:43 And just a quick note that your lookup array and your return array need to be
- 02:48 the same length, otherwise you will get an error message in xlookup.
- 02:52 So close the parentheses, hit enter, and that gives us Jamming as the client name.
- 03:00 And now we can copy this formula down.
- 03:03 But first, we have to lock the reference to the lookup value and lookup array.
- 03:10 We can do that with F4.
- 03:13 And then we can copy the formula all the way down.
- 03:15 And as I copy it down,
- 03:17 the return array moves one row down which is exactly what we want.
- 03:21 So you can see from these two examples that xlookup can do what vlookup,
- 03:27 and hlookup does.
- 03:29 Look up in a column and look up in a row.
- 03:32 Pretty powerful.
Lesson notes are only available for subscribers.