XLOOKUP in Excel | Excel Tutorials for Beginners

what's going on everybody welcome back to another video in this excel tutorial we'll be looking at x lookup [Music] now if you don't already know what x lookup is it is a new feature in excel to kind of replace the lookup or to be a much better option at least in my mind as a much better option than vlookup and so if you're someone who's either used vlookup a lot and you're trying to you know learn this new option or if you've never used it before this video will be super helpful because i'll walk you

through kind of the options and what x lookup can do as well as the difference between x lookup and vlookup but before we get into the tutorial i want to give a huge shout out to today's sponsor and that is udemy udemy is the go-to place if you want a full-fledged course in excel i have three options of courses that i have taken on udemy so i'd highly recommend checking those out they are having a huge sale on all their courses during this time and so if you are in the market for a course i

highly recommend checking out udemy and getting one there now without further ado let's jump on my screen and start the tutorial alright so let's get me off the screen because we all know why we're here so i didn't include this in the formulas video last week because i knew this was going to be a large one and a lot of people are going to want to know how to do this what the difference between vlookup and xlookup is so it has its own dedicated video to it so let's get started it is a formula so

we're going to come in here in the cell we're going to hit equal and then we're going to start typing x lookup now i'm going to hit tab in just a second but let's read what this says it says searches a range or an array for a match and returns the corresponding item from a second range or array by default an exact match is used so really useful to know we'll talk a little bit more about that in just a second let's hit tab and it's going to complete it and it's going to start giving

us or it's going to tell us what our input values need to be we're going to have our lookup value we're going to have our lookup array our return array and then some optional things like if not found so if your option isn't found you know what will be um you know the the output that it gives us a match mode and a search mode and i'm going to show you kind of how to use every single one of these things as you can see at the very bottom i've kind of already set up all

of the instructional instructional content for this video and so we'll kind of get through all these different scenarios so let's just start really quickly with how to use it very simply with the lookup lookup array and return array so we're going to come in here and we're going to give it our lookup value now toby flinderson right over here and a3 is going to be our lookup value so that's who we're going to be searching for now we're going to hit comma and now we're going to be needing to look up uh or to input

our lookup array now an array is just a you know a range basically so we're going to do this is where it's gonna be searching for um that value this is where it searches for a3 so here's toby flenderson here's toby flenderson so it will find it in this array right here then we're gonna hit comma and now we need to give it the return array what it's going to return on that row when it finds it so we're going to return his email keep it really simple so what it should do and let's call

this parenthesis what it should do is it should take toby flenderson it's going to search in this column or in this array and then it's going to return the email when it finds toby flenderson so it's on toby flenderson is on row 6. so it's going to find toby flenderson it's going to come over here and it's going to return toby flenderson at dundermifflandcorporate.com that's what it should do let's see what it actually does it says enter and it returns it now if we drag it down like this it'll apply it to all of these

names right here and it works exactly how it's supposed to um again if you have never used vlookup you don't know how good you have it okay vlookup was extremely useful but just a bit complicated and i'll talk about that near the end of the video when we compare vlookup to x lookup but just know that if you're using x lookup for the first time you're just getting into using excel you guys have it good okay so just know that um now let's go over here to x look up multiple rows because you can return

more than one output with um with x lookup so let's go right in here and we're going to basically write the exact same thing as we did before so let's write x lookup we're going to do toby flenderson as our value we're going to search here and we're going to do something a little bit different this time we want to include our end date and the email so what we're going to do is we're going to start here we're going to go down all the way to the bottom of end date and then we're also

going to include the email and when we do that it will in in the output give us a row or a column for end date and a column for email so an output for both so let's hit enter and now we can see that we have the end date here and the email here now one of the downsides or or something that i'm not a huge huge fan of is well first off i love that you can do this that's fantastic but it have to be right next to each other so you're only going to

get that output exactly how it is in the columns so if i went and did this range i would include all of that so yeah you know let's just for example let's pull that down here so let's take this and put it right here if i did instead of zero or o2 to p10 if i included age to email this whole range and i hit enter it's all going to be included so you know that's one of the small downsides of of that functionality of when you can use multiple rows is that it's going to

use the rows exactly as they are you can't really customize it within the formula you can move around these columns to how you want it so that is something to note and again you can pull this down and it'll be applied to all of those names let's go over to x lookup exact match so let's open this up we're going to do equals x lookup as we've been doing and we're actually going to be looking at the if not found and the match mode both you know on this tab right here so let's do what

we've been doing before we take our value that we're looking up we take the array that we're looking and we're going to do the email and you know as you can see this says toby flender not toby flenderson so what we are going to do is we're going to hit comma and if it's not found you can return um a value or a string that you want to return now for simple purposes or for simple instructional purposes we're gonna do not found and then we're gonna close that off so let's do this and toby flenderson

was not found and so it was returned not found if toby flinder was actually in this full name then it would have returned the email and then if along the way you know one of these was not part of it then you know we would have uh we would have had the not found all right so let's go right up here we're actually just going to copy this because i want to reuse it and then we're going to go right here and hit a comma now this is our match mode option and so we have

four different options that we can choose from a zero is an exact match and that is by default that is what we have or what we use then there's a minus 1 that's an exact match or next smaller item then there's a 1 which is an exact match or next larger item and then there's a 2 which is a wild card character match now we're gonna do that and we are going to um you know try this out and it's not gonna work and not just because i forgot to put a4 um it's doing it

because it's searching for beasley but if there's not a wild card option already put in here it doesn't recognize it so we need to indicate where that wild card needs to be so we're going to do a double apostrophe or quotation marks we're going to put an asterisk right here and then do another one and we're going to hit an ampersand so we're going to have an ampersand right here and what that's going to say is anything that comes before a4 anything that comes before beasley is okay doesn't matter what it is as long as

it has beesley at the end that is going to be okay so we're going to have pam that comes before beasley and that's going to tell it and it's going to say okay i know that anything that comes before beesly is alright and so when we hit enter is now going to return the output that we are looking for and we can include that on these as well now this one is meredith and so meredith is at the beginning so we have meredith palmer so we can actually take this and we're going to put this

at the end put the ampersand right here and now it'll work and the exact same thing for kevin malow right here kevin malone so it just didn't include the n e at the end and so it's still going to work if we include that asterisk at the end now i know i said we were looking at search order but i'm actually going to kind of give you an exact match first and then search order but it's just kind of easier to show it over here so i'm going to do x lookup i'm going to look

up this value do a comma here's the range this is our start date that's going to be looking for and i want to return the full name now no value in here has one one two thousand but what we can do is we can do comma and then a comma for the match mode and do an exact match or next larger and i know this is in the exact match part but it kind of refers to search order a little bit where it searches for the next largest value that's that's what that number one represents

the next larger value so we have one one two thousand and if we look right here the next value above one one two thousand is one five two thousand and so it should return angela martin let's see if that works and there it is now let's look at the actual search order so let's do equals x lookup this is the value that we want to be searching for and we're going to be looking at this start date and comma and we want to return the name now let's get over to search mode now the search

mode performs a search starting at the first item so at the very top going down so by default it searches from first to last but you can reverse that and do search from last to first or you can do a binary search which is where it sorts in ascending order or sorts in descending order and that's with the actual value and so we won't be able to show this binary search or ascending or descending because our values are the same but if we had different values and we were looking up i'm using this next largest

we would be able to show that but i'm going to show you this search from first to last and last to first so let's put in by default and this is what it would be search from first to last what the default would be so it starts at the very top it goes down and finds the first 5 6 2001 and returns toby flenderson now if we go in here and we hit minus one that is going to search from last to first so it's going to start at the bottom and go to the top

and the first one that it finds is michael scott so that's that first one starting from the bottom and then the michael scott right there so these two the exact match and the search order can kind of be combined into this one right here we're using this one which is you know exact match or next larger and you can include that in this binary search in this one as well all right now let's head over to the x lookup horizontal i think we're only have a few left yep x like a horizontal then we'll do

x lookup with sum and then i'm going to show you the vlookup at the end so let's go right here let's say equals x lookup the value that we want to be searching for is february that's what we're looking for hit comma and where do we want to search to find february we want to search in these calendar months and then we hit another comma and now we're going to be searching for paper so let's do paper and we'll hit enter and it found february and it returned paper right here and we can do that

for paper printer and manila folders and so it's going to give us the 310 the 40 and the 118 from february now let's go right over here to x lookup with some um i actually it's basically a carbon copy of this uh let's take this over here real quick and place it right there because it's the exact same thing except at the end we're going to use i'm going to show you how to use sum with the x lookup at the same time now we're going to be using the formula sum and so we're going

to sum and then within the sum our first number is going to be an x lookup and then our next value is also going to be an x lookup so let's do x lookup and now we're going to search for our very first value oops our very first lookup value so we're going to go to i1 and then we're going to search this again and we want whatever value oops goes into that so let's close that parentheses and now we're going to do a colon and another x lookup and now let's do march so now

we're going to search for march we're going to do our search range where we're searching for that march and we want the paper as well and let's close that and then we also need to close that parenthesis so now we are basically adding this february and this march so it's going to be 310 plus 150 it's adding those two values and it should be uh what 460. so let's see if that is our output and it is so you can do this with a lot of things not just some but you're able to use x

lookup within different formulas if you're searching for a specific value in a specific value in another cell you can add those together using x lookup which is honestly it's pretty great so let's go over to vlookup so i wanted to show you this because i wanted to show you where it came from and what we used to do unless you are continuing to use vlookup and what we can do now so axlookup i just showed you kind of everything but super quickly i'm going to show you how vlookup used to work in a super short

way so that you can understand how it used to be used and how it is used how x lookup is used now so let's go in here and we're going to say equals and we're going to do a vlookup and so we have a lookup value and so we're going to click this we're gonna hit comma just like we did before and now we're gonna do a table array and the table array is a little different in that you're searching an entire area so let's do uh h2 all the way through o oops o ten

so that's what that's what our table array is going to be then we're gonna do a comma and now we have to do a column index number which number are we going to be searching for which value are we going to be searching for in here and so we want to search for 8 because this is 1 2 3 4 six seven eight we wanna return that email and we're searching for the name right here in this very first column so we have that comma and we're gonna do eight and then in the range lookup

you can do true which is an approximate match or false which is an exact match and we'll do false i don't know why it's not auto auto doing it but there we go and now we will do it and it's going to return it just as we had it a lot of people uh i guess not everybody but some people didn't like and the reason why they created x lookup you had to do those ranges and if you ever went in here and then we let's say we um added another column which happens to data

now it gives us completely different different data so let's say for whatever reason we added address so now we have these people address well now it's going to give us a different value it's going to have this end date because if we go in here now it doesn't now the 8th is this end date and the 9th is this email so if you have a vlookup that you use for um you know a calculation or a table that you've created or different things in excel you then have to go through here and manually change this

and so a lot of people didn't like that because if you you know needed to change data or you needed to change something or add an additional column you'd have to go back and fix all of your vlookups they wouldn't just automatically move with it which is what happens with x lookup and just to prove this let's go back to the very first one which is the x lookup and right now the email is looking at o2 and through o10 we're just going to insert right here and that will be our new column we'll do

address oops address and notice that it hasn't changed and why is that because it auto changed for us from p2 to p10 understanding that it wanted to stick with when something was inserted here wanted to stick with the original data of the original array that was selected and so x lookup does that work for you and it makes it a little bit easier to automate things and create these processes in excel without having to go fix it later which you had to do with vlookup so that is it for today i hope that you know

how to use xlookup a little bit better now that you have watched this if you enjoyed this video be sure to like and subscribe below and i will see you in the next video [Music] you