XLOOKUP in Excel | Paula Guilfoyle | Skillshare
Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (21m)
    • 1. XLOOKUP Class Introduction

      1:11
    • 2. Limitations of VLOOKUP

      2:18
    • 3. Xlookup

      6:29
    • 4. Xlookup match modes

      4:53
    • 5. Xlookup search types

      4:54
    • 6. XLOOKUP Class Conclusion

      0:49
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

24

Students

--

Projects

About This Class

Move over VLOOKUP - Here comes XLOOKUP

VLOOKUP is one of those functions’ users tend to use over and over.  At the start it is hard to get your head around it but once you do, it provides great value. However, it also has its limitations. For example, on its own it cannot look up to the left.  You cannot do a horizontal lookup. You cannot add new columns to the dataset for fear of breaking the formula.

XLOOKUP aims to solve the limitations posed by VLOOKUP, it will be easier to read and write and it will calculate faster. This function will be available in Office 365.

In this class, you will learn how to use XLOOKUP and we will examine the search types and the match modes.

42efbc70

Meet Your Teacher

Teacher Profile Image

Paula Guilfoyle

CPA, Online Educators, Lifelong Learner

Teacher

Paula is a qualified CPA with over 15 years' experience in the fields of Accountancy, Business Management, Process improvement, Internal Audit, Group accountant, Operations Management and Training. All across a broad range of industries and sectors. Paula has been Key Speaker at many Accounting Events where her talks on Excel are received very positively. Taken from her experiences in Accounting and business fields, Paula also has courses for those wishing to upskill, especially in the area of Spreadsheets, Bookkeeping and Accounting.

Paula is also the owner and author at https://theexcelclub.com/.  The Excel Club is the only Excel blog in the world where you can earn while you learn.  To read more about our Excel Learn and Earn Activities take a read of this

<... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. XLOOKUP Class Introduction: ex local soon to be available in Excel Tree 65 is going to make you rethink how you carry out your lookups. It's a new function, which is the successor off the local. In this class. We're going to look at the limitations off a local. You learn how to carry out a look up with ex look abusing an exact match on. We'll then explore the much mold on Duthie search types that are new to ex local. This class is a short class. It's only going to take you half an hour at the end of which you will no have to use excels . Newest harvest function X Look up. It's really exciting. The fellow cop has a successor. Vehicle has been around since the first version of Excel. It's the third most used function after Soma on average. Hi, my name is Paula and I am a C P. A. Attention. I've been teaching Excel online now for 10 years, and I'm excited that you're going to take this class with me. So let's hop in and get started on. Let's teach you how to use the new X look up 2. Limitations of VLOOKUP: in this video, we're going to look at the limitations off the existing feel. Look up formula we look up, comes with many limitations. Some of these limitations We can work around with other functions or combinations of functions, but that's a little bit of a pain. It's kind of very time consuming. Who needs all of that hassle when ex local will remove most of the limitations off they look up With that in mind, we're going to compare X look up against free Look up on the limitations that ex local pro moves. So if to start with the 1st 1 you cannot carry over and exactly local by default would be Look up. I guess what? The default setting in X local is an exact match So you can with X Look up. You cannot look up to the left will feel Look up. You can look up to the left with X. Look up as long as the arrays are off the same size. So the look up array and the return a rare the same size. You can really look up anywhere you worked. You can't use free. Look up for a horizontal look up you have to use hates look up, but with X local Pekan do a horizontal onda vertical look up. You can really add new columns to a table that uses the local. Has the V lock up could break When you add new columns with X, look up, it doesn't break. This is because there's no table array or column index defined in the function. It's not possible to Sarah from the bottom to the top of a column. Wolfie, look up with X local You concert from the bottom to the top on the top to the bottom. Bine researches Using vehicles are not possible. These are possible with ex local. You can carry out an approximate match if in the table, unless the table assorted smallest to largest. Would we look up when ex local plea can carry out this one out? Sorting out the table and this is really an amazing step forward, and finally, it's not possible to return approximate match value that higher you're that's lower. Using vehicle, you can select a return on approximate match of higher value Onda lower value with ex local 3. Xlookup: Hey, everybody, it's polar here from the exile club. I'm welcome to today's video. In this video, we're going to look at the brand new X local function. This function is going to be the successor off. The ever popular V Look up, even though I really look up is so popular it comes with its limitations on the X Local function promises to solve many, many off the limitations that are opposed by the local. At the moment. X look up is only available in Excel insider addition, but it will be rolled a wood in office 365 soon. So we're going to study this X look up in terms up an exact match. It's really, really simple, but it looks a little scary at the start, so let's look at thesis in tax. We have X look up and it's looking for a look up. Then it's looking for in a local array, a return array and then we've got these two optional items. We have match mode and we have search mode. Thes are new now it looks a little bit scary. Let's go through them. The look of value is the value that you are going to be looking for The local parade is where you're looking for the value. The return array is where the values that you want to be returned come from. That we got this match mode on our default match mode is exactly that is not the case would be. Look up. We can then do other match molds as well, such as much to a smaller value match to a larger value on. We have wild card matches. Then we have search mode, another completely new feature. The default is descended from the top to the bottom, but you can also search from the bottom to the top. Andi, in addition to that chicken search for buying was now okay. It looks really, really scary, but these last two items are optional. Therefore, to do an exact match, we only need to complete the 1st 3 parameters. The look of value, the look operate on the return, right? Let's look up the coast price off our banana items using the old be local on the old feel local first looks for a look of value which will select to be our bananas. Then it looks for a whole table or a so we can select our entire table. After that, it looks for a column index number. So what column is it that you want to return the data before then? We have this range. Look up, so we have to. If we want an exact match, it's not the default. Approximate matches default. So we have to select exact much this will. Then I returned the wrong column there. This then will return the coast price off the item. Bananas. Let's do this with our new X. Look up and see how it looks when Iraq's look up. First of all, we want our local value nice and simple. Then we want to look up. Ray. This is just the column or the road that contains the data where you find the look of value , not the entire table that is looking for in a return array. Well, we want to return our coast prices now. There's one thing to note here that's very, very important. Your look up array on your turn return or a most be the same size. If they don't, you are going to get in there. So if we were jewels to select for our return array down to F 10. Just to show you how this will return an error, we get a value there. Let's go back in an editor formula. So it takes the correct value and hit enter. And now we have pulled back the correct value. So if we go to the lemons, we can see the coals to price is seven cent. We can also look up to the left Now, this was a limitation off the view your cup that we couldn't look up to the left. But with X look up, we can lock up to the left. So let's take our region. We can look it up in our local ray and look, we take our return array to the left. We could take it to the rhymes. We could take it over, we could take it and it doesn't really matter. Wounds theory rays of the same size. So we have been able to returned up to the left from that ex local can also do a horizontal look up. They previously to do a horizontal look up, we'd have to use our hate look former and this will take our look up value and then we would need our table or a After that, we would need our road index number on. We can just close the bracket there and we see we get there. Now we can do this with our ex local food so we can look up our region. We can look up our products. We can then local our return array and we see we get the same values. We don't need another function. Here's something else that's really, really cool as well. When we do RV local, if we insert a column off data are vehicle points. We were looking to pull up the cost price, but now we have pulled in need because everything is moved over by inserting column. So RV local bus broke both our ex local instead in place. Now what? We haven't looked out. And if we just go back in here for a second, I'm pressed comma. We then have our exact match types. We could skip that, too, and we have our different search types as well. I'm not gonna go true them in this video, but there are additional things such as when you're searching for an approximate match under search molds that you can actually do with this amazing X look up functions 4. Xlookup match modes: in the last video, you were introduced to the exit local fortune, and we looked at the X and look up function with an exact match. But using an exact match, we could use thesis in tax, where the look of value only looked for the look of valued look. A parade on Duthie return right. We didn't look at match mode or search mode, and then this video. We're going to look at this match mode that the match mold will allow you select between different approximate match types. Let's take a look at this table of diver here and note the table of data is not sorted. It's a discount table, and it's based on the units sold at a discount is given based on the units sold, So if it's up to 100 units, they get a 10% discount between one and 207% between 205 100 to 15% of between 500,000. You get a 20% discount. Let's have a look up doing this with a V. Look up. So, Avi, look up, looks for our local value and then looks for our table array in her table array. We give it our index number and then we want an approximate match. Now we can select approximate match or we can leave a blank because it is the default now. Often look up. Does is go down through the table on the table must be sorted because as soon as it reaches a value higher, then the look of value it goes back. One I'm returns the percentage fruit, that particular number of units. So we know that the value is not correct. And to get the correct value, we'd have to combine this local put other functions. Or we could use the exit, look up function and the acts. Local function looks for a look of value. We have our local beret which does not need to be sorted. We have our return array on. Then we can select between our match mold and we've seen earlier that we left this blank and we got of exact much because it's the default setting. But if we go for the next smallest, it will return the 7% that we are expecting because it's not looking for a sorted list. So it knows that the 4 40 is between the 200 on the 500 therefore gets 7%. Now we couldn't look for the next largest. We could look to return if it is between. If it's between 205 100 that they get 15%. So the next largest we can do this again with ex local parties. So we select our look up value. Then we select our local beret. Then we select our return array. Now we can select our match with the next largest value on this will return for us the 15% that we were expected to do a wild current search. We also need to use the match mode. Take a look at this. Here we have P question Mark your s on. We need to look something up in the products on return that, say, the sales rep. When you're working with a wild card, a question mark donates one character, whereas the mass tricks donates multiple characters. When there's only one character missing or when the character is missing from within a word , you can put the Asterix or you can put the question mark in the actual word on your excellent local will work. So let's see our ex local. We want to look up this value. We know it's pairs. We want to look it up in our products. We want to return our sales rep and that what we need to define here is our wildcard character match. If we don't define that, we will get an ever another. While car character is when the first few letters of the last few letters are missing, it will still work for us. So what we need to do is we need to come, Captain eight or join with an ampersand, the ass tricks to the actual text to make at a local value. Then we select our look up array. We select a return array on We need to select our wild character match type on press enter that if we go meet our wild character match type, we will get in n a error. So it's important to remember that if you're doing a wild character search that you do change your match mold to reflect this in the next video, we're going to look at the search modes 5. Xlookup search types: in the last videos on match. We looked at exact match and we looked at approximate matched, so we had to look at the search modes in this video. We're going to look up at last fields the search types. So let's just bring up our ex local function to see what options are available. So let's say X, look up. We're gonna look up this value. We're going to look it up in this array. We're going to turn return our date column on. Then we have our much our search modes. Our default search mode is search first toe last. That's the same as the local. It'll start the top on a return values working from the top. We also then have an option to search last to first. So if you had duplicate values and you wanted to get the last value, you can easily do that by searching last to first. Then we also have the buying. A research is where things are sorted in ascending order or descending order. We're gonna look at the first toe last on the last to first in this video. So we want to do is get the first sale deed for the sales Rep. Kira and No, with this X look up value were actually carrying out a look up to the left because their date column is to the left our match mode, where we're going to get an exact match so we can skip that on our search type. Well, we can also skip entering in the value here because the default is search first to last. So we see our first sales invoice. State procuring is the first of January 2020. We can get the last sales invoice number two. So, as I said, if we had duplicate values and you wanted to get the last value, so we haven't Linda's name in the list a number of times on, we want to get the last invoice state so we could search last to first so we would select our mind this born and we can see. Then it has gone up, and it searched from the bottom to the top to return the last date for Linda. That's really cool, because it was something that you could not do in excel with the look of Now, what I want to also show you is that this X look up function works with the New Excel engine on the dynamic array abilities. Dynamical raise We haven't exactly looked at yet there another new awesome feature that's going to be available in Excel 365 It's not yet released to the general public, but it is going to be available on the will. Be an update added to the course. But we can do a dynamic array with Durex. Look up our look up value we can select an array for so we've selected instead of one look up value in a way off, look up values. We then need to select a local beret. So our normal look up array. Then we can select our return Ray, and then we can select our match mold, which we will leave as exact. I went select last to first, for example. Now we've only entered this formerly influence. But look what happens because this is an awesome example off dynamic grace and actually hasn't haven't covered him yet. It might be your first time to see them. When we press enter, we get this spill so the values actually spill down. Although we haven't put a formula into find lender It's bill down, using dynamic arrays that we can see. This is great when we select the cell or this cell. That means that it's a dynamic away and the former isn't pleased with in the actual sell. But we can actually still were fair to the cell and get a value from it so we can use it on a later stage. I call these dynamic array function spill functions because if there's something in a cell in the way, you get this spill error. When you have the spill error, you can select the obstructing cells really, really quickly and delete them and the spill formula continues to work. So that's just a quick introduction to dynamic arrays, while I'm showing you the search types on the X look up function. 6. XLOOKUP Class Conclusion: congratulations on completing this class. You should not be familiar with ex local and how it can be used to overcome many off the limitations off vehicle. As you've seen, it's a very versatile local production exit. Akhil promises to be a game changer when it comes looking up. Values in Excel on returning to corresponding value There are so many new features and functions yet to be released in exile Tree 65 that are really going to blow your mind. But X local is for sure ranked in The Highness of Awesomeness. Of all these new releases that are coming, thank you very much for selecting this class, and I do hope to see you in another class again soon. My name is Paula, and it has been a pleasure working with you.