0:00
hello and welcome to Tel's point in this
0:02
video we are going to have a quick
0:04
practice session of the xlookup function
0:07
in Excel if you are new to xlup function
0:10
I recommend checking out my other video
0:12
where I explain the xlookup function in
0:14
detail I will leave the link in the
0:16
description below and you can also watch
0:19
this video for a quick practice session
0:21
also you can download this Excel sheet
0:23
from the link in the description below
0:25
now let's get started so here we have a
0:28
sample data set of product ID product
0:30
name category price and stock and we
0:33
want to find the price of a product
0:36
based on the product ID so let's say the
0:38
product ID is p 004 which is the
0:41
refrigerator and we want to find out its
0:43
price so it's very simple we'll use the
0:45
xlup formula I'll write here equals x
0:49
lookup and then inside xlup what I want
0:54
so I want this product ID I'm looking
0:57
for this product and then comma look up
0:59
array where does this product ID come
1:01
from it comes from this array so I'll
1:03
select this one and then another comma
1:06
what we want to return we want to find
1:08
the price so I will select the price
1:10
column yeah and that's it if I close the
1:13
parenthesis and hit enter we have the
1:16
price of the product ID p004 which is
1:19
$35,000 it's correct let's say uh for
1:23
p 5 and hit enter and it says
1:27
$10,000 okay it's working fine but as
1:30
you can see if I enter a product ID that
1:32
is not in the data set let's say p 007
1:35
which is not here right if I hit enter
1:37
it gives some error so in that case I
1:40
want here to have a text of something
1:43
like not found so here right after this
1:46
one I'll have another comma and then we
1:49
have this if not found so here I'm
1:51
simply going to type the M message
1:54
not found and close it and hit enter and
1:59
as you can see P 007 is not found and
2:01
let me just change it to P2 and hit
2:04
enter and it says exact
2:08
$2,000 now let's go to the next sheet
2:11
here on sheet two as you can see we have
2:13
the sample data of employees and their
2:15
salaries and here we have the salary
2:17
range and tax bracket so for any of the
2:21
employee I'll enter the employee name
2:23
here and I want to find out the salary
2:25
of that employee and I want to also find
2:27
out how much tax that employee has to
2:30
pay or basically the tax bracket so
2:33
let's first uh get the salary of
2:35
employee so to get the salary of
2:37
employee I'll write equals x lookup and
2:40
then we have the lookup value I'm
2:42
looking for this employee whatever is
2:44
here and then comma lookup array which
2:46
means where does this employee come from
2:48
it comes from this data so I'll select
2:50
the employee column and then comma what
2:53
we want to return we want to return the
2:55
salary of the employees right and that's
2:57
it and then I can just close close the
3:00
parenthesis and hit enter and we have
3:02
the salary of this employee that is
3:04
$85,000 if I change the name to some
3:06
other employee let's say
3:09
Priya and if I hit enter we can see that
3:13
it has $60,000 exactly the same so it's
3:15
working fine now the next part is to
3:18
look for the tax bracket so for PR the
3:23
$60,000 which is greater than this
3:25
$50,000 so the uh priya's tax bracket
3:29
should be 10% so how do we calculate
3:32
that I'll write here equals X look up
3:37
and then what we are exactly looking for
3:40
we are looking for this value all right
3:42
salary value all right and then a comma
3:45
Now where does this salary value come
3:48
from not this one we are not looking
3:50
from here we are looking for the tax
3:52
bracket so this salary value comes from
3:54
here so i' select this column all right
3:57
and then another comma then we have have
4:00
the return array what we want to return
4:03
in this case we want to return the text
4:05
bracket so I'll select this one so this
4:08
array of text bracket and then a comma
4:11
if says if not found so let's just skip
4:13
it so I'll write another comma and then
4:16
after it says match mode so in this case
4:20
we are looking for exact match or next
4:23
larger item so which is represented by
4:26
one so I'll write here one and then I'll
4:29
just close close the parenthesis and hit
4:31
enter and it says 0.1 so we need to
4:35
change this format so here I'll go and
4:38
change this format to percentage if we
4:41
hit enter as you can see it says 10% we
4:44
can also remove these two decimal places
4:46
by clicking on this one as you can see
4:48
decrease decimal so Priya has to pay a
4:51
tax of 10% pretty simple so let's look
4:54
at some other employee let's say
5:00
hit enter Rohan Singh has a salary of
5:03
$70,000 and uh he has to pay a tax of
5:07
10% now let's take the last example so
5:10
here click on this sheet three and
5:13
inside the sheet three we again have
5:14
this product ID product name category
5:16
price and stock and we uh are looking
5:20
for multiple conditions this time so
5:22
I'll write here the name of the product
5:23
ID so once I write the product ID I want
5:26
the name of the product category price
5:29
and stock very easy right so to use this
5:32
I'm going to write equals x lookup and
5:36
inside the xlup we're looking for the
5:38
lookup value in our case it is the
5:39
product ID all right it's the product ID
5:42
and then a comma and then what are we
5:46
looking for next it is the lookup value
5:50
so where does this product ID come from
5:54
it comes from this one so I'll select
5:56
this column A2 to A7 and I'll I'll press
6:00
F4 to fix it and then comma and what is
6:03
the return array now here in this case
6:07
our return array is going to be
6:09
everything because we want the product
6:11
name category price and stock so I'm
6:13
going to select all of this
6:15
data and again this is B2 to E7 and I'm
6:18
going to press F4 to fix it and then
6:21
I'll write parenthesis and hit enter and
6:26
as you can see p 003 product name is
6:28
blender k $5,000 and stock is 50 if I
6:32
change and write something like p006 and
6:36
hit enter it's the washing machine and
6:38
we have the exact same data but again
6:42
just like we did in the first example if
6:43
I write a product that doesn't exist
6:45
it's going to throw an error to fix this
6:47
error we are going to have a if not
6:50
found message in that case we can simply
6:52
write uh not found and just hit enter so
6:58
p 0 9 not found but if we look at p001
7:03
it's there so that will be enough for
7:05
this video don't forget you can find
7:08
this practice sheet Linked In the
7:09
description below if you found this
7:11
video helpful make sure to like it share
7:13
it with your friends and subscribe to
7:14
the channel for more tutorials and I'll
7:17
see you in the next one
