That we're running uh uh yeah we're definitely running okay yeah so we are up and running uh good morning again guys we were having a little bit of technical issues there just uh getting uh joe's camera set up there well the screen sharing or anything else actually uh but the exercise files are in the video description let me just drop them down again real quick for you guys just so you guys can get prepared for the for today's uh x lookup and vlookup uh demo and then also a big update here again if you guys want.
Regular excel updates daily joe's dropping him on tiktok so i'm dropping his uh chat here or his uh link here in the chat i'll get that up on our kicker too later once uh you start going there joe again you guys want regular excel updates and see what and also see what joe's up to it's also like what you also do word powerpoint and everything right like any everything in between yeah yeah i even uh if i see something that's really cool like maybe some food or something yeah it's just overall uh growth and learning there it's it's a lot of fun.
Yeah so if you guys want to learn even more check it out um just some quick channel updates before we get started um the sql querying class we actually can condense that into one video uh we could have done two but we felt it we felt it was more effective as one giant video that was closer to three hours as opposed to uh two one hour videos uh we will be coming up though uh sharepoint fundamentals is gonna be starting on friday uh it's been split into two so there's gonna be a beginner uh this week and then advance next week again this is why i don't give out the entire release.
Schedule because things can change quickly because initially we were going to have sql beginner in advance but like i said we decided to condense the course into one thing um just also uh um there joe you kind of went to snap camera but no worries i'll fix it up all right no problem anyways the uh as far as other updates go um i did send out if everyone was asking if there was going to be a new uh.
Sequel video or like more advanced uh i did have a a a class uh that's gonna that's more in depth uh sql reporting services that's gonna go more in depth i actually have that out to uh christina again to see if she wants to make that for us it would be about four to six videos so that'll be coming soon and honestly as soon as as if as soon as we get those videos recorded we'll probably get them up on the site right away uh just because there was a lot of uh requests for more advanced uh sequel videos so we'll be.
Video get uploaded after the show gets live yes all of our shows are available immediately on the channel uh on our on our channel um we will we have a playlist where we put all this up as well so you'll all be able to see like all our old shows and everything like that as well uh i think the our most popular show is still the one with uh with uh chelsea i'm sure everyone wants to see the face behind the voice so to speak but that's all the updates uh for now um there's a ton of other stuff we got going on uh but i don't want to delve.
Into it too much because i could talk about the channel for 30 straight minutes but let's get to mugs you have one today joe oh i do i do uh i mean i've shown this but some of you are new on here so i thought why not again i'm a huge star wars fan and i got one that says respect your father yeah i got another one this is another christmas gift i got this is again from the same company eighth generation.
Um love the design um yeah it's it's literally my favorite company for getting stuff lately um you know what's surprising is that i'm like the biggest san francisco 49er fan and you know go 49ers we're going to beat the cowboys this this sunday i have a helmet behind me the one thing i don't have is a san francisco 49er coffee mug i have all this other stuff but i just don't have a coffee mug it's so weird anyways what's going on.
Yeah next year everyone give me that no i'm kidding i'm kidding and again we're not sponsored by the 49ers or anything like that i wish [Laughter] we got to reach out to them yeah all right well let's say hello to everybody in here uh davindra sierra um gills minoge hasina kamal good morning to all of you um i know a lot of you guys are joining us uh from across the atlantic or the on the other side of the pacific so uh welcome um yeah i have a i hope you guys are having a good evening good morning or.
Good afternoon whatever it is um joe what's going on your side of the world oh nothing really i was in san diego for the longest time it's so beautiful out there it's like paradise 70 degree weather so that was a lot of fun and doing you know what i do best doing my training so that was always fun yeah but yeah i'm super excited for today's topic uh the reason why is because a lot of people have used vlookup and h lookup um vlookup being vertical look up and hook up being horizontal and there's always been some limitation with it so i'm i'm.
Excited to show you how to use vlookup if you wanted to learn h lookup and we have time i do have something set up for
That as well uh but we'll kind of throw that to the side because it's the same thing except it's just the way you set up your data and x lookup though this is this is it this is the feature this is the the new lookup that you'll probably use more than anything else because there's no limitations so um yeah yeah we'll we'll uh you know we'll all right let's get started man.Exercise files in the uh in the chat again guys so you know download the files these are gonna be the ones that joe's gonna be using uh for the for uh what we have up come right now all right okay i'm gonna share my screen all right joe joined let me know when it's oh there it is all right here we go all right see you on the flip side awesome thanks everyone so uh what i want to do first is talk about our vlookup here now vlookups are really amazing when you.
Want to pull data from somewhere else right and then pull it in so what i set up here is just a quick little example of employee lookup now i'm going to zoom in just a little bit to the top here and what i want to do is i want to figure out when i put in my employee id how do i actually pull the rest of the information right so if i'm looking up for an employee i put their id in it tells me their last name their first name their department and even what their pay rate is so in order to do this i'm going to have to utilize vlookup but.
Before we do that there are limitations to vlookup and that's important to note because you have to have your data set up a certain way otherwise it's not going to work for you so the first thing that i need to do here is look at what are some of those limitations well the first limitation is going to be that your lookup value has to match your first column your leftmost column in the table now when i do that when i do equals vlookup you'll notice that once you have this open if i go over to my little fx to open up my help box here which i always.
Like to do it'll even tell you it says a vlookup looks for a value in the leftmost column so it tells you right here left most column of the table and then it returns the value in the same row from the column now by default the table also has to be in ascending order here so you'll see here sorted in ascending order so one second uh everyone's saying your your volume's a little low maybe you want to check your microphone volume real quick make.
Sure it's kind of maybe just max it out real quick yeah let's take a look at it it looks kind of mad here's the volume check your system volume real quick all right let's do this and then here i'll hide your screen while you're doing it yeah i didn't want everyone to see all the stuff yeah yeah that's why i'm still gonna see this if i could do this instead i think that can help out a lot more sorry about the type of difficulties people trying to uh joe's computer just fyi his the main computer.
Uses uh kicked out on them so we were scrambling today to get uh this is the third computer we're using today so uh yeah sorry about it don't mind me yeah don't mind joe okay so let's see if this is going to work get this so i hope it does work yeah and then yeah everybody hear me.
On this one i think it's still about the same level so you might want to see if you can get it up okay yeah it should be under system settings okay i think that's a lot better perfect all right yeah perfect and a lot louder right yeah okay let me just place my mic there this is how you know we're live by the way and this is how you know that we're.
Really you know tucked out in the chat right away okay so let's go back to it so as i was saying when it comes to vlookup you want to make sure that you have both right you have your sorted and ascending order left most column and your left most column matches so for instance my leftmost column is in a sorting order you can see that going from lowest to highest and not only that but you'll see here employee id right.
It has the same thing right here with the employee id it's the left most column so they're matching if it was not
Like this i would not be able to actually use vlookup and that's why it's so uh i think old school to use vlookup is because those limitations are still in place uh x lookup actually cuts those limitations it doesn't matter where the columns are it doesn't matter what order the information's in and x lookup is so much easier to use so let me show you vlookup first though just in case you don't have x lookup yet because they are rolling it out as a newer function.And then also i'll show you x lookup afterwards and just show you the difference and how much easier it is so the first thing we're going to do here is delete vlookup from there we're going to place our cursors into b4 so if you're following along feel free so i want to pull the last name that's what i want the information to be pulled back so the first thing i have to do is figure out what am i looking up where am i looking for it and when i find it what am i actually grabbing so i'm going to do equals.
Vlookup and press tab on my keyboard to open that parentheses now if you can write functions out real quickly you can write it out what i like to do when you start off writing these functions is i go to the top here next to my formula bar and i click on the little fx here the reason i do this is because it's i call it joe's help box it's not really called joe's help box it's called the arguments box right but i call it joe's help box because you don't have to worry about putting commas in you don't have to worry about closing the parentheses you just put your information into each of.
These boxes and not only that it tells you exactly what these boxes do so if i click into range lookup and i'm like what is that it tells me it's the logical value to find the closest match put in true to find an exact match put in false so really cool uh how we have this like help box i love to use it for my mac users out there unfortunately you don't have access to this so you will have to just write it out i don't know if that's going to ever change in the future but for my pc users.
You can access this and once again you just click the little fx in the corner here so looking at these different arguments here we have the lookup value now the lookup value is simply what are you looking up and i'm going to be looking up my employee id so whatever's in a4 is what i'm looking up i'm looking up the number 1054 right now and i can just type in anybody's employee id there now the next thing that it's asking this for is where are you looking for where's your table so i'm just going to simply select my entire table and i'm going to select my.
Entire table and you don't even have to include the headers you could include the headers if you want to i typically just take the data down i don't include the headers now the next thing it's going to ask me for is a column index number now the column index number what that is is it's the number of columns in that your data is located at so what are we looking for well we're looking for the last name so i'm asking myself which column is last name in.
Now i can see that it's in column b but it's not asking for a column index alphabet right a letter it's asking me for a number so i have to change my mindset here this is no longer column a b c now it's just one two three and i can see that since i'm looking up the last name i'm gonna count one two so what i want are the last names and that's in column two so i would put a two here now the last thing it's going to ask me.
For is what's known as the range lookup so as i said before if you don't know what something is just simply look at what it says here and it says that if i want the closest match put the word true if i want an exact match put false now just a little tip here i typically put a zero for false or a one for true it equals the same thing so if you want to put a one that equals true if you want to put a false that equals zero so now that we have just the roundabout idea of what we're.
Putting in these different argument boxes let's actually do that so for instance the first thing i'm going to do here is the lookup value i'm going to select a4 because that's what i'm looking up the table array right so the table array is going to be my table i'm going to select it and i'm using my keyboard shortcut ctrl shift right arrow ctrl shift down arrow to select the entire table now my column index number we know is a.
2 because well we counted we want the last name so we count 1 2. and it's already pulling up smith but you do have to put the range look up and as i said before we want an exact match i don't want excel guessing the last name i actually want it to be an exact match so i'll either put false or to save time i put zero which equals false right now before we move on most likely what i'm going to do is i'm going to auto.
Fill this so i'm going to want to absolute reference my lookup value which means lock it into place so it doesn't move and to do that you have to add a dollar sign in the front of the a and in front of the number or on your keyboard you can use what's known as f4 all right so if you see at the top of your pc you should see f1 through f12 and if you use f4 what it does is it adds the dollar signs in for you and there we go.
Now i click ok and look at that smith so i know employee 1054 is smith and then if i wanted to look for like who's employee uh 1290 oh it's cooper all right cool and if i look at 1290 that is cooper so it's working perfectly now i just have to do the other ones like first name department and payri so if i was to do first name right for instance i'm gonna once again just write it out and then i will show you how to autofill it.
So for my mac users this time i won't use joe's help box but for my pc users if you want to use it again this time feel free so i'm going to do vlookup and once again it's asking me for my lookup value by the way if this ever gets in your way you can move it it's just a little guide to tell you what you're looking at next like right now it's on lookup value but you can always just move it around i like to move it like right down here so my lookup value right is going to be whatever i'm looking up by which is a4.
I'm going to absolute reference that so put my dollar signs in so it doesn't move and then i'm gonna put my comma now when you put the comma you'll see it goes from lookup value to table right now it's asking you what is the table so i'm going to use those keyboard shortcuts from before i'm going to use a10 i'm going to click on a10 use ctrl shift right arrow ctrl shift down arrow and then i'm going to just highlight my table information up top and use f4.
On my computer to put in the dollar signs and if you want you can put the dollar signs in manually i just like to you know save as much time as i can and i'll put a little comma column index number so really what am i looking for i'm looking for the first name so now i have to see which column is the first name in so let's count it right one two three okay so i know the three is going to go there i put my 3 put my comma i'm going to use an exact match because i don't want it guessing so i'll put a zero.
And then i'm going to close it up and press enter and look how beautiful that is it works perfectly linda so i'm looking at cooper linda cooper 1290 i'm gonna put in just another one i'm gonna do one zero five six let's see what happens joe gonzalez so now i know my employee lookup is working perfectly now to save you time if you just simply put in your formula and you just drag across it's going to repeat joe joe joe joe right and i know you're probably all.
Cheering joe joe no i'm just kidding you don't have to but now we have joe gonzalez department but you're gonna notice it's saying joe's still because the one thing we need to change is the column index number it's still looking at column three so i'm just going to count where the department is one two three four change the three to a four and it updates it how cool is that and then i'll do it for the same one uh pay rate so i'm gonna count one two.
Three four five six seven eight nine so i'm going to change that three to a nine and look how awesome so now we have a fully functional employee look up here now i can just simply look a number up one three three three see who that is there we go pretty cool now h look up very similar i'm not going to go over it right now because i do want to show you x lookup i'm super excited for it but h lookup same thing if i was to go into uh.
Hlookup it's because my data is horizontal now it's not vertically going down the columns it's going across the rows so instead of looking for a column index number it's looking for a row index number so i'll show you real quick hlookup same thing it's asking me all the same thing my lookup value my table but look now instead of column index number it says row now all i have to do is just put that information in what am i looking up the.
Product code where am i looking for it in my table what row is it in well i'm looking for warehouse one so one two and then i'm going to put in an exact match and it gives me 150. so when i look up x200 and warehouse one we have 150. pretty awesome and then remember you can always drag that down if you make sure you absolute reference these.
But what i want to get to is x lookup this is an amazing feature x lookup first off forget about the forget about the limitations there's no left column this it doesn't matter where your data is it doesn't matter what order it is it will work not only that but it also nests the if function in the reason why the if function is so important is because let's go back to vlookup for a second what happens if somebody comes in and like oh i wonder who employee 100 is.
Well there is no employee 100. so people get really nervous about that they're like oh it says that there's n a there's some sort of error well there's not really an error it's just simply saying there's nothing applicable at that time it's not available so you can nest an if error function which is okay you know you can mess the if error function cool but and i can say something like um doesn't exist so for instance i'll do uh.
Not real or something whatever you want to write in there by the way you don't have to write anything specific but now i have 100 in there and says not real instead of the error so that's really nice to add that feature in but a lot of people didn't do that because you would have to nest a function nobody likes nesting functions but with x lookup it's actually already built into the function really cool so taking a look at x look up here i have some information some fake data and let's say i wanted to pull the division right so i want to look up this.
Person's name in this master data list and pull in what division they're in well i'm going to use x lookup because i can't use vlookup realistically look where the name is i'm looking up the name it has to be the leftmost column but it's not it's all the way at the end here so right away i can't use vlookup also look it's not in alphabetical order so it's not an ascending order can't use it but i can use x lookup so i'm going to do this i'm going to do.
Equals x lookup press tab on my keyboard and i will click on the fx just to show you all the cool little features first off the three main features you need only three because if you remember we would have to pick true or false for the range lookup true being the closest match false being the exact match well in x lookup it's automatically the exact match so that's already a cool benefit right there so all you need is to fill out these.
Three you don't need anything else unless you want to what are you looking up the name where are you looking for it in the names and what do you want to return the division how cool is that just three things simple things what are you looking up where are you looking for it and what do you want to return now underneath it you'll also see they did add if not found so that's your if.
Error right there they also added a match mode just in case you do need the closest match and they added a search mode this is by far my favorite one you can search for either the first or the last return which means if there's duplicates in there you can choose what you want to pull so with that said let me show you how this xlookup works first off what am i looking for this person's name.
When i find it where where do you want to look for it well i want to look for it in the names column so i'll select all the names okay so what are we looking for where are we looking for it and now what do we want the division so i'm going to select the division and that's it look at that immediately i have a result so i looked for kim west it said okay where's kim west can we ask him west oh there and.
Then it said okay you want to return what division and it tells me utility now all i would have to do is just click and drag down and boom how awesome is that now do make sure though you do absolute reference your um your columns so i'll i'll just do an f4 on that one because it is important so that your data doesn't move i know in this example it worked well but still you should you should always absolutely reference things.