Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,370 --> 00:00:11,940
Now I've shown you how you can combine the index and match functions to perform a powerful, complex
2
00:00:11,940 --> 00:00:12,570
look up.
3
00:00:12,900 --> 00:00:18,600
We're going to see how we can do exactly the same thing in a different way.
4
00:00:18,840 --> 00:00:25,980
When I say in a different way, I mean using two brand new functions in Excel 2021.
5
00:00:26,190 --> 00:00:29,490
And that is X lookup and X match.
6
00:00:29,700 --> 00:00:36,480
Now, as I said, these two functions are brand new to excel 2021, and X lookup is really another way
7
00:00:36,510 --> 00:00:40,380
of performing a look up very similar to index and match.
8
00:00:40,380 --> 00:00:44,820
So it has all of the same flexibility that we love about index and match.
9
00:00:45,090 --> 00:00:47,340
But it's a lot easier to construct.
10
00:00:47,490 --> 00:00:49,950
So let's use the same examples.
11
00:00:49,950 --> 00:00:53,790
But this time, instead of index a match, we're going to use X lookup.
12
00:00:53,970 --> 00:00:55,230
So let's click in.
13
00:00:56,010 --> 00:00:58,970
I'm going to tie pin equals x look up.
14
00:00:59,370 --> 00:01:04,650
Now, the first difference you'll notice here is that we do have a lot more arguments and it's only
15
00:01:04,650 --> 00:01:06,870
the first three that are mandatory.
16
00:01:06,870 --> 00:01:10,650
All of the others, the last three are all optional arguments.
17
00:01:11,160 --> 00:01:15,300
Now we're going to go through all of them just so you can see exactly what they do.
18
00:01:15,450 --> 00:01:20,220
But if you just wants to do a basic lookup, you could just use the first three arguments.
19
00:01:20,490 --> 00:01:22,950
So the first argument is look up value.
20
00:01:23,280 --> 00:01:30,780
So I look up value is going to be whatever we have in cell H5 comma, what is our lookup array?
21
00:01:30,870 --> 00:01:33,810
So where are we going to find Google Docs?
22
00:01:34,530 --> 00:01:36,390
Well, we're going to find it in the apps list.
23
00:01:36,390 --> 00:01:42,910
So this is our lookup array and then we simply need to specify the return array.
24
00:01:42,930 --> 00:01:44,520
So what do we want to return?
25
00:01:44,550 --> 00:01:50,250
We want to return the category, so all we need to do is select the return array.
26
00:01:50,790 --> 00:01:52,210
That is basically it.
27
00:01:52,230 --> 00:01:57,240
I could close off my formula, enter and I'm going to get my answer.
28
00:01:57,780 --> 00:02:03,420
Now, how much easier is that than doing the same thing using index a match?
29
00:02:03,540 --> 00:02:08,880
Now let's double click to go back into this formula because I did say I wanted to run through all of
30
00:02:08,880 --> 00:02:09,720
these arguments.
31
00:02:10,530 --> 00:02:15,990
So the next argument is, if not found, so we have some additional things we can specify here.
32
00:02:16,110 --> 00:02:23,970
So if the lookup value is not found in the table, we can choose to return a value that we specify.
33
00:02:24,480 --> 00:02:26,250
So that might be a piece of text.
34
00:02:26,490 --> 00:02:28,860
Remember, text needs to go in quote marks.
35
00:02:29,490 --> 00:02:31,800
So I'm I just want to say here, not found.
36
00:02:33,070 --> 00:02:36,160
Comma, I can choose what type of match I'm doing.
37
00:02:36,310 --> 00:02:38,320
Do I want to do an exact match?
38
00:02:38,680 --> 00:02:43,390
Do I want to do an exact match or next smaller item on next large item?
39
00:02:44,140 --> 00:02:45,790
Well, I'm going to do an exact match.
40
00:02:45,790 --> 00:02:47,920
I want it to exactly match Google Docs.
41
00:02:48,580 --> 00:02:53,200
And then the final optional argument that we have here is the search mode.
42
00:02:54,040 --> 00:03:00,200
So with this particular argument, we can choose which way or how we're searching through this table
43
00:03:00,220 --> 00:03:04,930
so I could choose to search from first to last or last to first.
44
00:03:05,110 --> 00:03:11,380
Now, if I do first class, that's type one as our final argument, it's going to return the result
45
00:03:11,410 --> 00:03:12,340
of productivity.
46
00:03:12,430 --> 00:03:17,650
Now we're going to do X lookup again to return the profit and the revenue, but we're going to do this
47
00:03:17,650 --> 00:03:19,570
in a slightly different way again.
48
00:03:19,750 --> 00:03:24,190
Now, so far, we've seen how we can create named ranges and use those in our formulas.
49
00:03:24,310 --> 00:03:27,580
But what about if I want to put this data in an Excel table?
50
00:03:27,820 --> 00:03:31,410
Well, what I'm going to do is click in my data and press control.
51
00:03:31,420 --> 00:03:34,150
T yes, my table has headers.
52
00:03:34,150 --> 00:03:34,880
Let's click on.
53
00:03:34,900 --> 00:03:40,630
OK, now I don't particularly like this formatting, so let's change that.
54
00:03:40,930 --> 00:03:45,190
That looks a little bit better, and I'm going to give my table a name.
55
00:03:45,820 --> 00:03:51,100
So let's call this app's underscore list and enter.
56
00:03:51,220 --> 00:03:55,060
So now what I could do when I'm constructing this X lookup, let's do it again.
57
00:03:55,070 --> 00:03:58,300
So x look up and look up.
58
00:03:58,300 --> 00:04:01,240
Value is what we have in Cell H5.
59
00:04:01,690 --> 00:04:04,360
My lookup array is a table this time.
60
00:04:04,960 --> 00:04:11,020
Now I've called my table apps list, so I'm just going to start to Typekit double click to select that
61
00:04:11,020 --> 00:04:11,530
table.
62
00:04:11,650 --> 00:04:12,610
Now it's selected.
63
00:04:12,610 --> 00:04:18,550
The entire table for my lookup array is just the app column.
64
00:04:18,760 --> 00:04:24,760
So what I can do here is press the square bracket, and it's going to show me all of the different columns
65
00:04:24,760 --> 00:04:29,920
of that table so I can then go through and just select the app column.
66
00:04:30,640 --> 00:04:34,120
Press the tab key to select it and close that bracket.
67
00:04:34,900 --> 00:04:38,680
So now I've isolated that column comma.
68
00:04:39,040 --> 00:04:40,870
What is my return array?
69
00:04:40,990 --> 00:04:42,010
Well, my return array.
70
00:04:42,010 --> 00:04:44,110
This time I want to return the profit.
71
00:04:44,650 --> 00:04:46,870
So again, I could use my table columns.
72
00:04:46,870 --> 00:04:47,980
Let's choose the table.
73
00:04:48,900 --> 00:04:49,960
Square bracket.
74
00:04:50,160 --> 00:04:53,010
And I want to choose the profit column this time.
75
00:04:53,450 --> 00:04:56,550
Typekit to select and close that square bracket.
76
00:04:56,650 --> 00:05:01,590
Now if I want to essentially skip over an argument so I don't particularly want to have any, if not
77
00:05:01,590 --> 00:05:06,660
found text, I can just press two commas to move on to the next argument.
78
00:05:07,500 --> 00:05:11,850
I want to do an exact match and I want to search first to last.
79
00:05:13,230 --> 00:05:14,070
Close the bracket.
80
00:05:14,640 --> 00:05:17,220
Enter and it's going to return my result.
81
00:05:18,000 --> 00:05:20,790
Let's double check to make sure that this is correct.
82
00:05:21,480 --> 00:05:27,630
So let's find Google Docs and I can see that yes, the profit is one one six six.
83
00:05:27,870 --> 00:05:33,210
Now for our final look up just here, I want to focus in on that search mode.
84
00:05:33,210 --> 00:05:35,610
So which way we're searching through our data?
85
00:05:36,270 --> 00:05:37,680
So let's do our look up.
86
00:05:37,680 --> 00:05:40,540
One more time I look up value is H5.
87
00:05:40,650 --> 00:05:41,760
I look up a ray.
88
00:05:41,760 --> 00:05:43,920
Well, I'm going to use my table range again.
89
00:05:44,790 --> 00:05:48,120
I'm looking up the app in the app column.
90
00:05:51,040 --> 00:05:58,210
The return array this time is going to be the revenue, so let's select our table and the column.
91
00:06:02,990 --> 00:06:08,270
I'm not going to put in any, if not found tax, let's press another comma to go on to the next argument.
92
00:06:08,900 --> 00:06:13,070
I want to do an exact match and I'm going to search first to last.
93
00:06:13,820 --> 00:06:14,660
Close the bracket.
94
00:06:15,040 --> 00:06:17,120
Hansa and I get my results.
95
00:06:17,330 --> 00:06:20,570
Now with that search mode searching first to last.
96
00:06:20,810 --> 00:06:23,740
That works perfectly fine for the data that I'm using.
97
00:06:23,750 --> 00:06:27,860
But what if I have Google Docs listed twice in this table?
98
00:06:27,980 --> 00:06:30,260
So what I'm going to do is on the bottom here.
99
00:06:30,260 --> 00:06:36,500
I'm just going to add another row for Google Docs, and I'm going to assign it to a different category.
100
00:06:36,500 --> 00:06:44,660
So let's say utility, it's going to be Google Docs again, and let's just give it a value of a 50000
101
00:06:44,660 --> 00:06:47,720
with a profit of, let's say, five thousand.
102
00:06:48,770 --> 00:06:52,560
Now I'm just going to expand my table out to accommodate that new data.
103
00:06:53,330 --> 00:06:58,010
So now effectively, I have Google Docs listed twice in this apps list.
104
00:06:58,190 --> 00:07:04,550
Now, currently, if we use the revenue as an example, it's show me the revenue of the first Google
105
00:07:04,550 --> 00:07:09,170
Docs that it finds in this list because I chose to search from first to last.
106
00:07:09,950 --> 00:07:15,950
But if we double click to edit this formula, what I could do is I could change this to something different.
107
00:07:16,400 --> 00:07:22,820
So let's do search last a first minus one and her answer, and we get a completely different result.
108
00:07:23,390 --> 00:07:29,720
This time it's searching from the bottom upwards, and the first Google Docs entry comes to is this
109
00:07:29,720 --> 00:07:30,640
one just here?
110
00:07:30,920 --> 00:07:35,330
So it's returning a result of 50000 for the revenue.
111
00:07:35,930 --> 00:07:39,530
So that's how that search mode option works.
112
00:07:39,680 --> 00:07:44,930
But as I said, in its most basic form, if you don't want to use all of those optional arguments,
113
00:07:45,200 --> 00:07:50,120
you just have three arguments look up value lookup array and return array.
114
00:07:50,150 --> 00:07:51,530
Super simple.
115
00:07:51,650 --> 00:07:53,720
So now we know what X lookup does.
116
00:07:53,720 --> 00:07:55,400
What does X match do?
117
00:07:56,030 --> 00:07:58,940
Well, X Match is fairly similar in many ways.
118
00:07:58,940 --> 00:08:03,860
We have a lot more arguments than we do when we're using just the basic match.
119
00:08:04,610 --> 00:08:10,580
So what I could do up here is if I want to go back to using index a match, I could do an index with
120
00:08:10,580 --> 00:08:11,990
an X match instead.
121
00:08:12,410 --> 00:08:14,360
Our array is the category.
122
00:08:14,570 --> 00:08:20,150
So let's select it and notice that because I have this data and a table is automatically going to put
123
00:08:20,150 --> 00:08:25,460
the table name and the column name in for me, row number.
124
00:08:25,560 --> 00:08:30,980
Now, if you recall, previously we were using the match function to automate the finding of that row
125
00:08:30,980 --> 00:08:31,400
number.
126
00:08:31,970 --> 00:08:36,410
But what I could do is use the new X match function instead.
127
00:08:36,560 --> 00:08:40,160
Again, the last two arguments here are optional arguments.
128
00:08:40,430 --> 00:08:43,520
Now I look up, value is what we have in H five.
129
00:08:43,880 --> 00:08:46,580
My lookup array is where I'm going to find that.
130
00:08:46,580 --> 00:08:48,320
So that's in this apps list.
131
00:08:48,950 --> 00:08:52,880
I can then choose a match mode, so I want to do an exact match.
132
00:08:53,330 --> 00:08:57,650
And then I also get that search mode option when using X Match as well.
133
00:08:58,100 --> 00:09:03,710
So I can choose if I want to search from first to last, last, a first or start doing a binary search
134
00:09:03,710 --> 00:09:05,420
and sorting in ascending order.
135
00:09:05,630 --> 00:09:08,510
So let's do laster first again, minus one.
136
00:09:09,770 --> 00:09:10,640
Close the bracket.
137
00:09:10,850 --> 00:09:11,450
Enter.
138
00:09:11,600 --> 00:09:17,360
I can see that I've forgotten to add an additional bracket on the end, so I'm going to accept the correction
139
00:09:17,900 --> 00:09:23,840
and it pulls back the category of utility because it's finding this entry fast because it's searching
140
00:09:23,840 --> 00:09:25,130
lost too fast.
141
00:09:25,220 --> 00:09:31,400
Now you can also use X match on its own when not combined with index, and it works very similar to
142
00:09:31,400 --> 00:09:33,240
the match function on its own.
143
00:09:33,260 --> 00:09:35,840
It's basically going to tell you what the row number is.
144
00:09:36,410 --> 00:09:38,420
So the arguments are pretty similar.
145
00:09:38,450 --> 00:09:43,580
Look at value lookup array as where we're going to find that which is just here.
146
00:09:44,860 --> 00:09:49,090
My match mode is exact, and I'm going to search let's do first class this time.
147
00:09:49,300 --> 00:09:55,270
Close the bracket, hit enter and it's going to tell me what row Google Docs resides in from the top.
148
00:09:56,050 --> 00:10:00,930
So pretty much the same results that you get with Match, but it's just a little bit more flexible.
149
00:10:00,950 --> 00:10:02,920
You have a few more options in there.
150
00:10:03,160 --> 00:10:09,190
So that is your introduction to two brand new functions in Excel 2021.
14458
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.