All language subtitles for 5. Using XLOOKUP and XMATCH

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French Download
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranî)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt-PT Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu
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.