Hi, this is Wayne again with a topic “Using Excel’s XLOOKUP Function”.
In this article, you’re going to learn how to use the x lookup function in microsoft excel for years and years, one of the most commonly used functions in excel was the vlookup function and there’s also an h lookup, and these are two very powerful functions. But the fairly new x lookup function blows both of them away and for many of you, you’ll never again need to use vlookup or hlookup. So what is the purpose of x lookup? It basically enables you to take information that you have and use that information to look up related information, so you can see here. I have an employee list for a fictional business. We have employee names at the left, we have their employee number department, salary, etc, and let’s say that i need to look up an employee based on their employee number, maybe every time they log into a website or fill out a form they put their employee number. I could then use x lookup to search this document for their employee number and then i could produce their name and in this case i also want it to produce the department that they’re part of and their salary. So i’m going to click here on h2 and i’m going to write my formula that uses the x lookup function, i’ll type equals x, lookup and, as i type you can see, excel recommends that i use the xlookup function.
I could just click on that to select it or i could keep typing next i’ll put the left parenthesis and, as always at this point, microsoft excel is giving me some tips to help me successfully write the rest of this formula. Excel is now looking for the lookup value. In other words, what is the information that i know well in this case i know the employee number right. In my example, i said the employee numbers are recorded, maybe when they use the internet or when they fill out a form. So that’s what we know. We know their number, but we don’t know their name department and salary.
So i could type in a specific employee number – perhaps this one here, so i could type that out right in the formula if i want to, but it’s more powerful in many cases to instead of typing it just click on a cell where you will eventually type The number or the item that you want x, lookup to look for, in my case i’ve already written the number it’s here at the left in cell g2, so i’ll click on g2 and of course i can change this number later. But the formula is always going to be looking at whatever is in cell g2, and that’s the lookup value that it will search for next, i put in a comma, you can see. Excel is now looking for the lookup array. Where do i want excel to search for whatever’s in g2? Do i want it to search in column a no? It’S not going to find this number in column a well? What about column b? No, not there either.
I need to select c because that’s where employee numbers are stored in this spreadsheet, you can see what excel did it put in c, colon c, in other words, all of column c i’ll put in a comma next excel is looking for. What’S called the return array, in other words, i know the lookup value i’ve specified where to look for that lookup value, column c. Now, what is it that i want to be returned? What is it that i want to see? Well, in this case, i want it to return the name of the employee, so that’s easy i’ll, just click on column, a just right here on the a it selects the entire column or again, i could type a colon a i’ll put in a comma now. What if it’s not found what, if excel searches all of these employee numbers does not find this number, so it can’t return a name. So what do i want it to return? Well, i could just skip this. I could delete that comma. Put in my right parenthesis. You don’t even have to put the parenthesis in at the right if you don’t want to tap enter and you can see it worked, so it works without putting in anything for if not found.
But in this case i think i do want to put something in there i’ll put in a quotation mark and i’ll just type. The word none and then another quotation mark, and then i could put a comma in and at this point all i need to do is tell excel what kind of a match am i looking for? Am i going to require excel to find an exact match to this employee number, in my case, yes, because with employee numbers, close doesn’t mean that much right, it’s pretty much got to be exact and notice that that is the default. So if you just skip this step, it will default to exact match, which is great and that’s a little bit of a change from vlookup. If you don’t want exact match, you can see the other options here. These are some really great options to have i’ll. Just put a zero, so it’ll be exact match and again i could have just left that blank i put in a comma and the very last step is what kind of a search do i want it to do? Do i want to search from the top to the bottom first to last, or do i want to search from last to first starting at the bottom, there’s binary search, sorted ascending, binary, search, sort of descending? These, ultimately, don’t matter that much so i’m just going to stop there at exact match. Put in my right parenthesis and again, i could have left off the zero. I could have left off the none in quotes.
I could have just stopped here instead i’ll stop here. After the zero tap enter on the keyboard – and you can see excel – has used that x, lookup formula to search column c for this number. It found it let’s find out where it found it. Okay, there it is, and it corresponds to nick robinson who’s in the communications department.
Okay, so let’s go back up to the top nick robinson, so it did work. It did find the right employee. Now, for those of you that have watched my other excel tutorials, you know that i don’t have to repeat those same steps for the next employee number. I don’t have to start the formula all over again.
All i would have to do is click on nick robinson and, if you think about it, it looks like i’m clicking on nick robinson, but i’m actually not. This cell doesn’t really contain nick robinson. It contains this formula which produces in this case nick robinson. So if i click here in the lower right corner of the cell on the auto fill handle that little green square, i can click and hold and pull down and it will copy and extend the contents of h2 down the spreadsheet.
So let’s go down and i’m not going to stop here with row. Six, i’m going to go one more to row. Seven and you’ll see why, by using the autofill handle notice what happened with my formula, it did copy the formula and paste it, but it also extended the numbers. It adjusted the row number. Basically, in h2 you can see the row number is g2 and in h3 you can see that the number is g3, so it extended that number it adjusted it up same with the rest of these. So pretty quickly.
I’M able to look up employee numbers and return employee names now this one here returned a zero. Why? Well it’s because g7 in this case is blank now. What if i just put in some random employee number – that’s not going to be in my list here. If i try that notice that it says none, and it does that, because i included that in my formula, but if it’s just completely blank instead of none, it just says zero. Now, what i just demonstrated of using x lookup to take an employee number and look to the left to find out the employee name that was not possible to do using the old vlookup vlookup is not capable of looking left to get its information. It has to look to the right, and so that’s one of the nice things about xlookup.
It doesn’t matter if it’s left right up down. It can look that direction and find the information. So, let’s try the same thing again, but this time with department i’ll type equals x lookup. I could base this one off of the employee name or off the employee number.
It doesn’t really matter i’ll, go with the employee number again, comma and once again i need to put in the lookup array, in other words, what column or what range should excel look in to try to find the employee number. Well, it’s column c. So i click on c to select the entire column comma. What’S the return array, what’s the information i care about that, i want to learn about and see in this case, it’s department, so i’ll click on column, d, i’ll put in a comma in quotes.
I’Ll put the word: none in case a number is typed in, but it doesn’t match any of the employee numbers and then, in this case i’m going to skip the match mode and the search mode. They don’t really matter much in this example and if you recall, x, lookup defaults to an exact match. So it’s going to try to find an exact match of this number and that’s exactly what i want so i’ll. Just put in my right parenthesis: tap enter on the keyboard and nick robinson works in the communications department.
I can use the autofill handle and i can just double click on it to extend it down the page. So i don’t have to click and drag. You can see it found the department of each of these employees and we can do the same thing with salary x, lookup, the lookup value again could be the employee number, but i want to show you that it doesn’t have to be so i’m going to select The employee name this time, comma lookup array: where are they going to find the employee names here in column a this time, comma? What’S the return array? What’S the information i’m looking for that, i want to see it’s salary, so that’s column e. At this point, i should put in the right parenthesis, but i’m just going to tap, enter to demonstrate that you don’t really have to and it produces the salary.
I can just double click on the autofill handle to fill that down the page and we’re good to go. I’M just going to copy paste another employee number there just so you can see it is working in all of the cells here. At this point, if i want to, i can select j and on the home tab home ribbon in the number group.
I can change that number to be currency, so you can tell it’s a salary and i might also want to select this range and kind of set it apart from the rest of the spreadsheet, by putting in a background color. So now i could use this part of the spreadsheet as a form that i could use to look up, an employee number or a series of numbers all at once so i’ll, just clear out the contents of those cells and i’ll just take a few of these Numbers paste them in and you can see, the names are looked up, the department and the salary and they’re all produced so that i can see them. So i hope you can see some of the potential value in using x lookup. It’S so much easier, more straightforward and powerful to use than the old vlookup, and it also replaces hlookup as well. I love the fact that it can look either left or right, and then i also like that it defaults to an exact match now, as microsoft often does, with its newer functions, they’re rolling it out gradually it’s available right now in microsoft, 365 versions of excel and Also these other versions.
Eventually, i would expect it to show up in all modern versions of microsoft excel if you don’t have x lookup in your version of excel. You should definitely check out my tutorial on vlookup and then, of course, please do check back and watch this video once you do have access to xlookup thanks for watching this video. I hope you found it to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified.
When i post another video, if you’d like to support my channel, you can do that through my patreon account or by buying channel merch, and you can learn more about those options in the description below this video .