How to use the XLOOKUP Function in Microsoft Excel - Beginners Tutorial

hi there welcome to teachers Tech my name is  Jamie and it's great to have you here today   I want to show you how to use the function  XLOOKUP in Microsoft Excel so you can use   x lookup to replace the functions vlookup  or hlookup in Microsoft Excel so I'm going   to go through five different examples here  today kind of showing you it in action and   I'm going to put a link to the spreadsheet  also so you can download it and follow along   with me so let's get started on how to use  the xlookup function in Microsoft Excel   foreign before we get started here today  I just want to point out that the X lookup   function is not available in Excel 2016 or 2019  but you could come across that if it's somebody   in a newer version did create an X lookup  function and then you were opening that one

so why would you even use the xlookup function in  Microsoft Excel well you can use it to find things   whether it be in a table or range by row so let  me give you a simple example of using xlookup you   could use vlookup for this also but this is one  of the ways you could replace vlookup now if I   wanted to search in this column for a country and  return a dial code so look at this so if I just   was going to type something like the United States  in here you can see how it returned the plus one   and if I look at this United States plus one so  it went down this column here searching by Row   for the United States what I typed up in here and  it returned this so let's start how to use the X   lookup function to do this so if you're following  along with the worksheets that I gave you can

see the formula using the X lookup is already  in it so if you're following along now you can go   ahead and just hit delete and then build it as  I go law so let's start with building this first   simple one and to do this we're actually going to  use the insert function so it's just in this first   one that I'm going to be using the insert function  but I'm going to just choose it and you can see we   do a search for it so you don't even have to type  the whole thing and you'll find the function right   here so I'm going to go and open up the function  arguments now and this is where we can start so   what do we want to have happen well I've selected  F2 where I'm putting this formula in so this is   where it's going to return the results of what I  build here what's the first thing I need to look   up so I'm going to type in a name of a country  here and I want it to return the code so we need   to look up and what you can see it's flashing in  this one I could select this or I can just click   here and it goes to E2 so it needs to look up  what's an E2 and then I'm going to move down to   the next argument and it's going to look up the  array so what's the array so it's going to be   from China to Mexico that I need to select that  range so I'm just going to click on China hold   down and drag and select from A2 to a 11. so what  is going to be the return array so I need to click   in the next one and the return array is going to  be this right here so I'm just going to go ahead   and select this now at this point I can hit OK and  notice that since I had the United States selected   it returns plus one and now if I was going to pick  a new one so I'll type Mexico and so it went down

this range that I told it to and it went found  Mexico and it returned this prefix here the plus   52. so that's the first example of how to use x  lookup in a formula in a very simple one so let's   move forward and how we can find multiple items  using X lookup in this next example of X lookup   I'm going to show you how it can return multiple  items so in this example this is what we're going   to build when we put an employee ID number in  so if I put 4 5 4 6 4 in here like this and hit   enter it returns an employee name and Department  in it so return multiple items and let's say if   also now I put in a number that's not there it  can actually return not found and I'm going to   show you how you can actually add this to your  X lookup function so again this is where the X   lookup function is going to live and I'm going  to hit delete and we're on the second tab right   now where it says multiple items so let's start  building this so this time I'm just going to start   in B2 and I'm going to just use the equal sign  here and I'm going to start typing X lookup and   I'm going to go ahead and select it because it  was already found and I'm just going to type out   the formula and select everything that I need so  what is the place where it's going to look up so   I showed you that the employee ID here is going  to be what's going to be what it's looking for   so I need to say click on this spot so it's going  to look up A2 and what is it going to do next so   if I put a comma and then it's going to have  our lookup array so it's going to look up our   employee ID just like that so then what we need  to do is add a comma and then our return array and what I can do for the return array is Select  both columns so this entire range is going to   be from B5 up here to c14 down here so I can go  ahead and just finish this like this and hit enter   uh now notice it says n a because this number is  not notice so if I was going to type in 2 3 4 5   it can find this because I haven't added that  other argument of not found uh so how do you do   that well if I go back to where the formula was  and let's say I want to add a little bit X or to   this all I have to do is add another comma and  then you can see it says if not found so what I   what do I want it to do I can just type not found  but you do need to add your quotation marks first   and then you can go ahead and end your quotation  marks and then hit return so now if I go ahead   and enter a random number you can see it says  not found because it looked through all these   employee IDs didn't find it and then we said if  it didn't find it that it can put back not found   so that's another example how you can use multiple  find multiple items use using the X lookup so in

this next X lookup example I want to show you how  you can type a number in here so let's go ahead   and just put a random number in here and it's  going to return a tax rate so notice it doesn't   have to be an exact one it just still found the  tax rate too for it and so it searched down this   column right here and return the matching one  and I just want to point out it went from right   to left if you ever use vlookup before you know  you can only go from left to right when you're   doing that type of formula so let me show you how  you can build this one using X lookup so we're on   the third tab now fines matching and I'm going to  go ahead and delete the formulas I'll just click   into this cell hit delete and we'll start again  so to start with I'm going to use the equal sign   and what's going to be the formula well it's the  X lookup so there it is right there so the first   thing that we need to put is the lookup value  and it's going to be whatever we put in here so   this is the lookup value and we're going to put  a comma so what is it going to look up well it's   this right through here so it's  going to look up the amounts right through here   and then I'm going to put the an X comma and it's  going to have a return rate so this is where it's   going to return the tax rate so I need to select  the tax rate like this so what I need to do next   is continue I'm not done yet I need to add a comma  so then it's if not found what I need to do is put   a 0 in here so if nothing is found I then it needs  to go to the match mode so now here are my options   for the match mode is it going to be what numbers  you can see from 0 to negative one to two   so we're going to put a 1 in here which means  the function will look for the exact match so   just like that but what happens if it doesn't find  the exact match then we have some more options   we're going to choose this time to go with another  one so search for Stella so let's put another one   just like that and now we can go ahead and close  this and let's test it out so let's pick a number   and I'll pick a number where it'll be between this  here so I'll pick 195 000 so 195 000 hit return 33   percent so it searched it didn't find the 195  000 uh so it went through all these different   options here didn't find the exact match but then  it found the next larger so that's what we told   it to do as we created this uh this formula using  the X lookup so in this next example you can see

if we look at the formula there's going to be two  x lookup functions this one is nested inside of   this one so what is this going to do so if I go  ahead and type in quarter let's say three it can   return the information so it's doing a vertical  lookup and it's also doing a horizontal lookup   so you can use x lookup in this situation to  replace H lookup all right let's get started   on how to create this formula so I just want to  point out the nested X lookup formula is in each   of these three spots because I do copy it over  and I am going to be using the dollar sign just   to be able to hold the columns in place when  I copy I'm going to go ahead and delete these   three right here and we can start from scratch so  what do we need to start with we'll start with the   equal sign and start our finding our X lookup just  like so what's the first thing that we're going   to look up we're going to look up gross profit so  it's just going to be holding picking gross profit   I don't have to put the dollar sign with this  because as it copies over I do want it to move   over to the right so I'm going to put my comma  what is it going to look up it's going to look   up this column right through here so this vertical  column I do need to hold this into this column so   I'm going to put the dollar sign I here so just  go ahead and add right in front of each one like   so and add a comma this is where we can add our  nested X lookup so if I start typing our X lookup   just like so and now what are we looking up this  time well we're going to look up at C3 so what is   C3 it's going to be whatever I type in right here  now I have to hold this in spot so I need to put   the dollar sign uh in front of the C3 so when  I copy it over it stays looking up C3 for the   quarter now I can go back I put my comma what is  it looking up for quarter well you can see across   the horizontal right through here so I'm going to  actually select all through here just like this so   it's going to be looking up this part right here  so I do need to put my dollar signs in front that   both C's here so just go ahead and put in front  of the G also and what does it need to return well   it needs to return it needs to look through all of  this just like that so it's going to go from C6 to   G6 17 and I do need to make these with the dollar  sign the absolute so I'm going to go ahead and put   them in again just like that so I now I can close  my brackets so we're going to need two at the end   here because one closes this first one uh here  and then another one to make sure I close both   arguments so I'm going to hit enter I have to end  it and let's copy this over so I put this in one   spot and now if I go ahead and just drag it over  grab that little green little square and drag it   over and I'm just going to change the formatting  on this back to percent just like that and now we   can try using something else so let's try total  and you can see what it went through it found it   found total over here and returned the different  things I told it to look for it found the gross   profit so if I look over here the 135 400 the net  profit over here 104 000 and it returns earned the   profit so I can I said thirty percent but that's  because my decimals uh were only to one place   there so now I can change it so it all matches so  that's how we looked up using X lookup we looked   up a vertical and horizontal uh way so you can  replace your H lookup using this with the nested X   lookup function so in this next and final example  we're going to be summing something up using X

lookup we're actually going to be using two nested  X lookup and how this works is whatever I put   in for this start it will add up every the range  between the two or so including the two so if I   was going to type in grape here so if I type grape  and then I was going to type cherry here what it's   going to do is it's going to add up the 61 42 15  30 13 12 and 990 and it's going to give me a total   for that so we're going to be using as I said two  nested X lookup and the sum function so let's get   started with this so I'm going to go ahead and  just delete this you can see there is the formula   that we have and we're going to rebuild it from  scratch again we'll start with our equal sign   and I'm going to put the sum function in first  so what is it I need to sum well this is where   I'm going to put my first nested X lookup I'm  going to choose X lookup here and what is going   to be the first thing I look up well it's going  to be the B3 so that's going to be the start of   this and I need to put my comma where is it going  to look up well it's going to look up between B6   and 10. so right here so  at this point I can go ahead and just do another   comma like this and what's going to be my return  well it's going to be the totals here so I'm going   to just go ahead and I can end this first one  with a bracket I don't need to add dollar signs   or anything I'm not copying this over and at this  point I'm going to add I'm going to put my colon   in and then add another X lookup here so I'm going  to go use the X lookup up here and then so I can   go and keep going to the next one so what is the  next thing I'm going to look up well it's the end   so it's going to be C3 and I'm going to use my  comma and select this again and I'm going to use   the comma again and what is it I'm going to select  well it's going to be the total over here and at   this point I can close it with the two brackets  just like that so it's going to sum up now the   sums on the outside here so it's going to look up  this X look up this and then sum   up so I'm going to hit return let's test this out  if we go say apple and we are just a grape like so   hit return 90. 66 and you can see that's what  the total of those two if it was extended to   everything here and we put Cherry it's the whole  total of everything there so that's how you would   use the sum function with two nested X lookup  functions so go ahead and play with the different   ones that I created here for you here today  hope you like this explanation of X lookup and   if you're able to use it if you have the correct  version of this let me know what's working what's   not working for you in the comments below what  else you want to learn about Excel thanks for   watching this time on teachers Tech I'll see  you next week with more Tech tips and tutorials