It's been a while since I used XLOOKUP for anything.
So, as it often happens, what it started as a simple brush-up, it suddenly turned into an idea for a new video.
Now, no matter if you're here for learning from scratch or else, just enjoy this video. I hope it may help you.
------------Timestamp-------------
00:00 - Intro
00:30 - XLOOKUP Basics
04:42 - Why XLOOKUP over V-HLOOKUP?
07:58 - Matches Options
13:13 - Multiple Criteria
15:01 - COMBO= XLOOKUP + other Excel function
17:30 - NESTED(Two-ways) XLOOKUP
20:12 - Ending
--------LINKS---------
GITHUB FILE: https://github.com/beppedataworld/EXCEL-LOOKUP
CARDS
- 4:20 = Card Python vs Excel [Video "Python vs Excel"]
- 13:28 = Card Python vs Excel [Video "Python vs Excel" FILTER]
------------Credits-----------
Music track: "80x Deep Synthwave" by SigmaMusicArt (https://pixabay.com/it/users/sigmamusicart-36860929/)
Brick Neon wall:
https://www.freepik.com/free-ai-image/oil-spill-brick-wall-surface-texture_144487238.htm#query=neon%20brick%20wall&position=2&from_view=keyword&track=ais_user&uuid=aeabdf85-b6f4-4d11-817f-4ee3326d5009">Image by freepik
Category
📚
LearningTranscript
00:00Excel again, my dears! Because you know, no matter how good you are in Python, R,
00:06etc, your boss will always ask you for an Excel sheet. So we better know how to
00:12master it, and today we will see probably the Excel final boss, the XLOOKUP
00:18function. In the very first minutes you will learn how to use it in its basic
00:22form, but if you stay for more, there will be several levels to unlock. So, let's
00:28the challenge start! To understand how XLOOKUP works in its basic form, we're
00:33gonna use an example in this small data set. I want to find, for instance, the
00:37price for a specific model of smartphone. In this case, I have two ways to do that.
00:42First one, click on FX, Insert Formula, and in the Guide Interface opening, select
00:49XLOOKUP, and then all the inputs elements. In bold are the only three
00:54mandatory inputs, but we're gonna use the traditional method. Just use XLOOKUP
01:01in the formula, and insert the three elements. First of all, lookup value, which
01:07is the model of the smartphone, so 1 plus 11. Then, I select IsArray, so the model
01:15column, and I locked with F4. Now, I only have to select the return array, which is
01:23the column of all the price, so that I can, and once again, I lock it with F4.
01:29Then, I return, and here we are. Then, I can copy and paste the formula and drag it
01:34for all the other ones. And that's how XLOOKUP works in its very simplest way.
01:39But now, before leaving for this basic form, let me show you how to insert my
01:44lookup value, because this is very important according to the use you will
01:48do in the future. Phase one, as we see, is just select the lookup array in the cell
01:53inside our dataset, but we can even just address one single cell outside it. Here,
02:00I got a formula, which is referring to F2. I didn't have to lock it. Why? Because
02:06this way, I can just test a formula just by references to a specific element. But
02:13if I want to test it in several ways, I can create a list, a scroll-down list, and
02:19now I do that, I go in a cell, this is where my formula is addressing to F3, so
02:25the usual formula. I go in this cell, I go to data, I go to data validation, and here
02:32I select list. Now, I only have to insert the interval or range of my input data, and of
02:38course, add the model. Now, I click OK, and here we go. I got a small arrow pointing down,
02:48and then here I select whichever element, Samsung Galaxy A14, for instance, and here
02:53we see the price changes. I choose another one, Motorola, and it changes again. In the
02:58last way, we select as a lookup value a cell which is not included in the same sheet, but
03:05another one. In this case, I'm going to use the smartphone dataset, the complete format. In this
03:10case, I'm using the first element, so the first model, which is not included in Xlookup. So, the
03:18first one after Oppo A78, which is Xiaomi Redmi Note 12. Now, how to do that? It's simple. I know
03:26the same formula in the same cell, and now we can just follow in the other page. In fact, I go on
03:36smartphone dataset, and here we see I got the same formula. Now, I just have to select the first cell
03:45that I see, which is this one, Xiaomi Redmi Note 12. As my value, my lookup value, is on another
03:52page and is not included in the other sheet, I have to select as lookup array the same array here,
04:00and finally, I select as a return array the price column. Before I click return, just pay attention
04:09to the way the text is displayed. It's different because I'm working on a table. If you don't
04:16remember how to use that or you've never known that, I suggest you to follow my previous video,
04:21Python vs Excel. Now, let's return, and here we got the price for the Xiaomi. Congratulations! Now
04:30you know how to use Xlookup in its basic form. You deserve a pat on your back. However, if you've
04:38come this far, maybe you're willing to come a little further. Let's focus now on the main
04:44advantage of Xlookup over Vlookup in each lookup. Now, you see here the same table as before. I
04:53already filled in the Xlookup formula in both directions because this is the first advantage.
04:59It can work in both vertical and horizontal directions. The only thing that changes, let's zoom in, is the
05:08lookup array. In this case, it's B2, B13 because it's, of course, a column. In this case, on the
05:15other hand, it's between B18 and M18 because it's horizontal. Let's see here another limitation,
05:24which is this one. If I have this company, I select my lookup value as B2, then I select
05:32all the interval, as we say, as array, and as my return array, I select the number of the column.
05:40In here, I select 2 as the number of the row. But what happens if I drag my formula as I do with
05:48Xlookup? There are some mistakes. Erase value, not available error. Why? Actually, that's a mistake
05:57here because we know that all the models are real present here. So, how to fix it? Well, we need to
06:05insert a fourth input, which is false, exact match. And now, if I drag it, everything works.
06:16The same, I don't do the same here, but it's false even in Xlookup. Look at here. I select
06:24as my lookup value a name of a model which doesn't exist. In this case, Beppe's phone.
06:31Of course, you don't find it here in my lookup array, in B2, B13. So, now what's the problem here?
06:39That if I don't do anything here, now let me cut it,
06:46you see? Anyway, once again, it raises this not beloved error. Now, before leaving you,
06:55another advantage here. Vlookup and Hlookup, every time you select your return array, as you see here,
07:03for instance, in the formula, always have your model, so your lookup value, to be on the leftmost
07:12or the topmost return array. That's not happening for XLookup, for instance. In fact,
07:19look at here, I say XLookup, I select, once again, my lookup value and now my array.
07:28My array is always the same, so nothing changes here. I lock it and now, as my return array,
07:35I'm supposed to select price and that's it. However, I can do something else.
07:41I can even add another column here. I say look at into brand name, for instance.
07:49Now, without even specifying any other information, I got my value working.
07:54Now, what happens if my lookup value is not found inside the same lookup array? We've already seen
08:09that we can even match another model, for instance, another smartphone here, from another page,
08:14but then we have to select the same array on the other page. But now, I want to do everything here
08:21and let's take an example. Probably, you've seen many of these examples with a date format.
08:26It's the same principle, for instance. Now, instead of the price, I want to see,
08:32I want to try to buy a new smartphone and I create seven different range of price in absolute value.
08:39How can I do so here is I want to select the price as my lookup value now and I want to select
08:47this lookup array, not in this column price, but in this other one. As you can see, many of these
08:55price are not included here. Actually, none of them. So, it will raise a value error, as we've seen before.
09:02But let's see how to do it in a different way, but just by using the fifth input element, which is
09:09the partial match. Let's see how to do that. Once again, I select my lookup
09:17formula and now, as I told you before, now my lookup value is the price.
09:24And, of course, his lookup array is no more this one, but this one here, then I lock.
09:31Now, as my return array, I select this colored one and lock it. What happened now? It's an error,
09:42of course, as I told you. But what if if I say, OK, in case you don't find the exact match,
09:48just leave it blank. But I'm not satisfied. I just want to find another kind of match.
09:55In this case, I want to find the next small item. What does it mean? We'll see immediately.
10:05As you can see here, I say whenever I find this price, in this case, the price is a fifty four
10:10thousand ninety ninety nine. So it was supposed to be close to sixty thousand. However, I say
10:17let's go to the smaller one. The smaller equivalent is to fifty thousand. So the return array is
10:24between 40 K to 50 K. If I now drag my formula. It works for all of them. I actually can even
10:36go on with all the other elements inside here and we will see that now I have immediately what I
10:43was looking at. I can do the opposite one instead of the closest, the lowest one I can select.
10:50And just let me see here in this example, which is the same, I just say for the cheapest one from
10:57the poor, for the poor to the costly one. And now in the formula I select one and I didn't select
11:04minus one, which means that now four thousand ninety nine, it's an upper, which is, of course,
11:11the lowest one here. So sorry, the higher one last time was 40 K and now it becomes on
11:20sixty thousand on the upper. Another option we have is to find the same exact match, but not just
11:28for numbers or date, but even for words or better partial match for words. For instance, let's take
11:35an example here. I want to find out. OK, now I can even the formula here. I want to select the
11:43word any smartphone model, which includes the word or the three letters one. And that's in the
11:53model column as my lookup array. And finally, return all my brand name for that model. Once again,
12:01I select now no more exact match zero, no more partial match one or minus one, but two. And
12:10finally, I even select the order minus one. This is another more. Anyway, as you can see here,
12:18just to make an example, one is including the model for one plus eleven, one plus
12:24north. Then there is Apple iPhone because these two asterisks before and after one means any words
12:34or any letters before and after. So in this case, even Apple can work. And so if I select this way
12:43and I select the last element, of course, it will return me nothing for. So the brand name nothing.
12:53And that's what happened here. If I change the formula and I say one, for instance. OK,
13:00it's included here. I select the first one, which is one plus one eleven five G.
13:07So the brand name is one plus. And that's, in fact, what I get.
13:13Separate the wheat from the chaff, the men from the boys.
13:20Now, let's handle multiple criteria just by using X lookup,
13:24something that we already seen with the filter function in my last video.
13:30Let's go. Let's look up for buying an Oppo smartphone with a price of 1899. So these are
13:36my two conditions. So this is my first lookup value and this is my second lookup value.
13:43Now I need to select my lookup array and I select brand name and with another commercial
13:50fee. I select even the price. Remember, I can do because they're not each lookup or lookup,
13:57so I can handle not contingent columns. Once I done, I select my final return array
14:05and the result is Oppo E78. Now let's do the reverse. And I want to find out two input.
14:14So the brand name and the price just by starting from the model,
14:19which is Oppo E79. For doing this, I'm just have to use X lookup as usual.
14:25I select as my lookup value Oppo E78 and its lookup array. But now I need to select the other
14:34two return array, which is brand name and price. In this case, just for having all information
14:42inside three different columns, I select the whole return array. OK, in fact, you will see
14:49criteria, which is the name of the whole table. Once I done. You can see the same information
14:56I got here. Oppo here and E89 here. So don't go anywhere, folks. It's just getting exciting.
15:04Now we know enough how to use XLOOKUP in order to make a combo with another Excel function.
15:12In this case specifically, I just want to find out only Samsung smartphone in a range between
15:20$30,000 to $40,000. And at the end, I just want to select the model which is the most expensive.
15:27So the closest to the $40,000. How to do that? In two steps. In the first one, I just want to
15:33select all the Samsung model just by using filter. We select the whole data set. OK.
15:43Then I select all my condition and I'm going to use three conditions with the asterisks.
15:52The first condition must be that the model, but the brand name must be equal to Samsung.
15:59So I'm here equal Samsung. Now the second criteria is that I want to find out only price
16:12higher than $30,000 or equal $30,000. And again, I see the same thing with $40,000, but now lower.
16:24Now I want to come back to the former and sort according to the price in ascending order. Sort.
16:34With the third column, so the price, and I don't have to specify the ascending order.
16:40Now I got all the price and now I only want to select this one. I finally can use XLOOKUP.
16:53So
17:08it's not enough anyway, because I want to select the last one. So in this case,
17:14I first of all say, in case you don't know any match, do nothing. Exact match. And finally,
17:20the last element. And here we got Samsung Galaxy Note 10 Lite,
17:26which is actually the most expensive in my range.
17:35Hey you, still here? Well, great, because it's time for the last challenge. Nested XLOOKUP,
17:43or as someone other call it, Two-Side Lookup. But what are we talking about? Well, to put it simple,
17:50we're going to use an XLOOKUP inside another XLOOKUP. In the first one, instead of just getting
17:57one single value or a set, I'm going to output a whole column or array. And as you can understand,
18:04this array will be used as my return array for the other XLOOKUP. Confused? Let's see an example.
18:12First of all, I'm going to use three different drop-down lists. Now, in the first step,
18:19I'm going to use XLOOKUP and I'm going to select the price as my lookup value. But instead of that
18:28now using the price column as my lookup array, I'm going to use the horizontal side for the table
18:35array. A bit strange, isn't it? But you will understand. Now, as my return array, I select
18:41the whole column. Now that I got it, first of all, let me copy all this part. And now, let me run it.
18:53As you can see here now, I don't have one single element, but a whole array. And this array will be
18:59used now in the second XLOOKUP as a first input, so the return array. And in fact, I'm just going
19:07to use XLOOKUP once again. This time, I select my model. And as usual, I go to my model column.
19:19And here, let me copy and paste my XLOOKUP for the other formula. We close it and I got my result.
19:28So, two sides because I start in the first step just by using an horizontal array. And then,
19:36I proceed with another column, another vertical array. But I can do the opposite in this model
19:43here. That's an alternative example where I'm just using another nested XLOOKUP but in inverted
19:51order. Now, why did I use just this list? Because you now can change in real time. For instance,
19:58I select brand name and you see everything changed. Now, I change even this other one
20:03with Apple and this time we change here. So, you can see all the tables now are connected.
20:12Wow, you made it till the end. You worked your ass so hard and I'm so proud of you.
20:21Now, you mastered XLOOKUP. So, maybe liking or subscribing this channel won't be such a tough
20:28challenge, right? Anyway, see you all next time. Bye-bye!