How to use the XLOOKUP Function in Microsoft Excel - Beginners Tutorial
How to use the XLOOKUP Function in Microsoft Excel - Beginners Tutorial
This video tutorial will show you how to use XLOOKUP in Microsoft Excel. The XLOOKUP Function can replace the VLOOKUP and HLOOKUP functions in Microsoft Excel. In this tutorial, I will show you why to XLOOKUP and give you 5 examples to see it in action. From a simple exact match to nested XLOOKUPs with the SUM function.
0:00 Introduction
0:50 Why use XLOOKUP in Microsoft Excel
1:38 Find exact match with XLOOKUP
3:35 Find multiple items with XLOOKUP
6:56 Add more arguments to XLOOKUP function
9:46 Used nested XLOOKUP to perform vertical and horizontal match (replace HLOOKUP)
13:54 Use the SUM function with nested XLOOKUP
Practice Worksheets: https://leveragingdigitalinc-my.share…
Content
0.3 -> hi there welcome to teachers Tech my name is
Jamie and it's great to have you here today
5.22 -> I want to show you how to use the function
XLOOKUP in Microsoft Excel so you can use
10.8 -> x lookup to replace the functions vlookup
or hlookup in Microsoft Excel so I'm going
18 -> to go through five different examples here
today kind of showing you it in action and
22.68 -> I'm going to put a link to the spreadsheet
also so you can download it and follow along
26.76 -> with me so let's get started on how to use
the xlookup function in Microsoft Excel
32.82 -> foreign before we get started here today
I just want to point out that the X lookup
38.7 -> function is not available in Excel 2016 or 2019
but you could come across that if it's somebody
44.88 -> in a newer version did create an X lookup
function and then you were opening that one
51 -> so why would you even use the xlookup function in
Microsoft Excel well you can use it to find things
57.12 -> whether it be in a table or range by row so let
me give you a simple example of using xlookup you
63.72 -> could use vlookup for this also but this is one
of the ways you could replace vlookup now if I
69.78 -> wanted to search in this column for a country and
return a dial code so look at this so if I just
76.26 -> was going to type something like the United States
in here you can see how it returned the plus one
82.86 -> and if I look at this United States plus one so
it went down this column here searching by Row
90.3 -> for the United States what I typed up in here and
it returned this so let's start how to use the X
97.08 -> lookup function to do this so if you're following
along with the worksheets that I gave you you can
101.94 -> see the the formula using the X lookup is already
in it so if you're following along now you can go
107.64 -> ahead and just hit delete and then build it as
I go law so let's start with building this first
114.54 -> simple one and to do this we're actually going to
use the insert function so it's just in this first
119.1 -> one that I'm going to be using the insert function
but I'm going to just choose it and you can see we
124.74 -> do a search for it so you don't even have to type
the whole thing and you'll find the function right
130.2 -> here so I'm going to go and open up the function
arguments now and this is where we can start so
137.64 -> what do we want to have happen well I've selected
F2 where I'm putting this formula in so this is
143.94 -> where it's going to return the results of what I
build here what's the first thing I need to look
149.1 -> 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
155.82 -> to look up and what you can see it's flashing in
this one I could select this or I can just click
161.94 -> 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
168.84 -> the next argument and it's going to look up the
array so what's the array so it's going to be
174.54 -> from China to Mexico that I need to select that
range so I'm just going to click on China hold
180.78 -> down and drag and select from A2 to a 11. so what
is going to be the return array so I need to click
192.3 -> in the next one and the return array is going to
be this right here so I'm just going to go ahead
199.62 -> and select this now at this point I can hit OK and
notice that since I had the United States selected
207.78 -> 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
215.22 -> this range that I told it to and it went found
Mexico and it returned this prefix here the plus
221.82 -> 52. so that's the first example of how to use x
lookup in a formula in a very simple one so let's
229.38 -> move forward and how we can find multiple items
using X lookup in this next example of X lookup
238.02 -> I'm going to show you how it can return multiple
items so in this example this is what we're going
243.36 -> 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
251.76 -> enter it returns an employee name and Department
in it so return multiple items and let's say if
259.68 -> also now I put in a number that's not there it
can actually return not found and I'm going to
266.58 -> show you how you can actually add this to your
X lookup function so again this is where the X
272.94 -> lookup function is going to live and I'm going
to hit delete and we're on the second tab right
278.28 -> now where it says multiple items so let's start
building this so this time I'm just going to start
284.04 -> in B2 and I'm going to just use the equal sign
here and I'm going to start typing X lookup and
290.28 -> I'm going to go ahead and select it because it
was already found and I'm just going to type out
295.08 -> the formula and select everything that I need so
what is the place where it's going to look up so
301.8 -> I showed you that the employee ID here is going
to be what's going to be what it's looking for
308.58 -> 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
316.5 -> if I put a comma and then it's going to have
our lookup array so it's going to look up our
323.82 -> employee ID just like that so then what we need
to do is add a comma and then our return array
336.24 -> and what I can do for the return array is Select
both columns so this entire range is going to
343.92 -> be from B5 up here to c14 down here so I can go
ahead and just finish this like this and hit enter
355.62 -> 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
361.98 -> it can find this because I haven't added that
other argument of not found uh so how do you do
368.94 -> 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
375.9 -> this all I have to do is add another comma and
then you can see it says if not found so what I
382.98 -> what do I want it to do I can just type not found
but you do need to add your quotation marks first
389.34 -> and then you can go ahead and end your quotation
marks and then hit return so now if I go ahead
396.66 -> and enter a random number you can see it says
not found because it looked through all these
403.62 -> employee IDs didn't find it and then we said if
it didn't find it that it can put back not found
410.46 -> so that's another example how you can use multiple
find multiple items use using the X lookup so in
418.5 -> this next X lookup example I want to show you how
you can type a number in here so let's go ahead
424.8 -> and just put a random number in here and it's
going to return a tax rate so notice it doesn't
431.58 -> have to be an exact one it just still found the
tax rate too for it and so it searched down this
437.88 -> column right here and return the matching one
and I just want to point out it went from right
443.94 -> to left if you ever use vlookup before you know
you can only go from left to right when you're
449.82 -> doing that type of formula so let me show you how
you can build this one using X lookup so we're on
457.32 -> the third tab now fines matching and I'm going to
go ahead and delete the formulas I'll just click
462.9 -> into this cell hit delete and we'll start again
so to start with I'm going to use the equal sign
468.66 -> and what's going to be the formula well it's the
X lookup so there it is right there so the first
475.26 -> thing that we need to put is the lookup value
and it's going to be whatever we put in here so
480.96 -> this is the lookup value and we're going to put
a comma so what is it going to look up well it's
486.84 -> going to look up this right through here so it's
going to look up the amounts right through here
492.24 -> 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
499.08 -> going to return the tax rate so I need to select
the tax rate like this so what I need to do next
506.22 -> 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
512.94 -> 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
522 -> for the match mode is it going to be what numbers
you can see from 0 to negative one to one to two
528.78 -> so we're going to put a 1 in here which means
the function will look for the exact match so
535.68 -> just like that but what happens if it doesn't find
the exact match then we have some more options
543.42 -> we're going to choose this time to go with another
one so search for Stella so let's put another one
550.74 -> just like that and now we can go ahead and close
this and let's test it out so let's pick a number
558.42 -> 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
566.94 -> percent so it searched it didn't find the 195
000 uh so it went through all these different
574.92 -> options here didn't find the exact match but then
it found the next larger so that's what we told
581.22 -> it to do as we created this uh this formula using
the X lookup so in this next example you can see
589.32 -> if we look at the formula there's going to be two
x lookup functions this one is nested inside of
595.8 -> this one so what is this going to do so if I go
ahead and type in quarter let's say three it can
603.84 -> return the information so it's doing a vertical
lookup and it's also doing a horizontal lookup
610.56 -> so you can use x lookup in this situation to
replace H lookup all right let's get started
617.46 -> on how to create this formula so I just want to
point out the nested X lookup formula is in each
624.84 -> of these three spots because I do copy it over
and I am going to be using the dollar sign just
630.84 -> to be able to hold the columns in place when
I copy I'm going to go ahead and delete these
635.94 -> three right here and we can start from scratch so
what do we need to start with we'll start with the
641.34 -> equal sign and start our finding our X lookup just
like so so what's the first thing that we're going
647.1 -> to look up we're going to look up gross profit so
it's just going to be holding picking gross profit
653.16 -> I don't have to put the dollar sign with this
because as it copies over I do want it to move
658.62 -> over to the right so I'm going to put my comma
what is it going to look up it's going to look
664.92 -> up this column right through here so this vertical
column I do need to hold this into this column so
672.24 -> I'm going to put the dollar sign I here so just
go ahead and add right in front of each one like
679.86 -> so and add a comma this is where we can add our
nested X lookup so if I start typing our X lookup
688.44 -> just like so and now what are we looking up this
time well we're going to look up at C3 so what is
696.42 -> 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
704.1 -> the dollar sign uh in front of the C3 so when
I copy it over it stays looking up C3 for the
712.38 -> quarter now I can go back I put my comma what is
it looking up for quarter well you can see across
718.74 -> the horizontal right through here so I'm going to
actually select all through here just like this so
725.76 -> it's going to be looking up this part right here
so I do need to put my dollar signs in front that
731.16 -> both C's here so just go ahead and put in front
of the G also and what does it need to return well
740.46 -> it needs to return it needs to look through all of
this just like that so it's going to go from C6 to
748.02 -> G6 17 and I do need to make these with the dollar
sign the absolute so I'm going to go ahead and put
754.98 -> them in again just like that so I now I can close
my brackets so we're going to need two at the end
760.26 -> here because one closes this first one uh here
and then another one to make sure I close both
766.56 -> 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
773.1 -> spot and now if I go ahead and just drag it over
grab that little green little square and drag it
779.34 -> over and I'm just going to change the formatting
on this back to percent just like that and now we
786.54 -> can try using something else so let's try total
and you can see what it went through it found it
793.56 -> found total over here and returned the different
things I told it to look for it it found the gross
799.74 -> profit so if I look over here the 135 400 the net
profit over here 104 000 and it returns earned the
809.28 -> profit so I can I said thirty percent but that's
because my decimals uh were only to one place
815.52 -> there so now I can change it so it all matches so
that's how we looked up using X lookup we looked
821.94 -> up a vertical and horizontal uh way so you can
replace your H lookup using this with the nested X
830.82 -> lookup function so in this next and final example
we're going to be summing something up using X
839.28 -> lookup we're actually going to be using two nested
X lookup and how this works is is whatever I put
845.16 -> in for this start it will add up every the range
between the two or so including the two so if I
851.94 -> was going to type in grape here so if I type grape
and then I was going to type cherry here what it's
859.74 -> 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
868.68 -> for that so we're going to be using as I said two
nested X lookup and the sum function so let's get
876.36 -> started with this so I'm going to go ahead and
just delete this you can see there is the formula
882.84 -> that we have and we're going to rebuild it from
scratch again we'll start with our equal sign
887.94 -> and I'm going to put the sum function in first
so what is it I need to sum well this is where
894.3 -> I'm going to put my first nested X lookup I'm
going to choose X lookup here and what is going
901.44 -> 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
907.08 -> this and I need to put my comma where is it going
to look up well it's going to look up between B6
914.4 -> and 10. so it's going to look up right here so
at this point I can go ahead and just do another
922.02 -> comma like this and what's going to be my return
well it's going to be the totals here so I'm going
928.56 -> to just go ahead and I can end this first one
with a bracket I don't need to add dollar signs
934.62 -> or anything I'm not copying this over and at this
point I'm going to add I'm going to put my colon
939.84 -> 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
948.66 -> 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
953.58 -> 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
961.32 -> the comma again and what is it I'm going to select
well it's going to be the total over here and at
968.4 -> this point I can close it with the two brackets
just like that so it's going to sum up now the
975.6 -> sums on the outside here so it's going to look up
this X look up this X look up this and then sum
980.88 -> 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
991.8 -> hit return 90.66 and you can see that's what
the total of those two if it was extended to
1000.38 -> everything here and we put Cherry it's the whole
total of everything there so that's how you would
1007.82 -> use the sum function with two nested X lookup
functions so go ahead and play with the different
1015.5 -> ones that I created here for you here today
hope you like this explanation of X lookup and
1021.44 -> if you're able to use it if you have the correct
version of this let me know what's working what's
1025.52 -> not working for you in the comments below what
else you want to learn about Excel thanks for
1030.08 -> watching this time on teachers Tech I'll see
you next week with more Tech tips and tutorials
Source: https://www.youtube.com/watch?v=tPaXEZRh9_k