0:00
hello and welcome to tutorial's point in
0:02
this video you will learn about the X
0:04
lookup function in Excel this video is
0:07
perfect for beginners so you will be
0:09
able to follow along easily also you can
0:12
download this practice sheet from the
0:14
link in the description below now let's
0:16
get started xlookup is a powerful new
0:19
function in Excel that makes searching
0:21
for data simpler and more efficient it
0:23
was introduced to overcome the
0:25
limitations of older functions like V
0:27
lookup hookup and index match now here
0:30
we have the simple data set of a student
0:33
name and the second column is their
0:35
pending fees now here again I have the
0:37
student name and I want to find out the
0:40
pending fee for this particular student
0:42
so how do we use the X lookup function
0:45
here let's start by writing the function
0:47
I'll write here equals then X look up
0:51
and then parenthesis now first we have
0:54
to insert this lookup value which is
0:57
what we are looking for so we are
0:59
looking for this person so I'm going to
1:01
select this cell all right then comma
1:04
now after this we have to write the look
1:07
up array this means what is the source
1:10
of this person in simple terms so we can
1:12
see that this person belongs in this
1:14
very student name column so I'm going to
1:17
select this student name column all
1:19
right and then in the formula I'll write
1:21
comma then we have to write the return
1:24
array which means where is our result it
1:27
is in the spending fee column right
1:29
because we are looking look for the
1:30
pending fee and yeah that's it now I'm
1:33
going to close the parenthesis and hit
1:36
enter and we have the pending fee for
1:38
Joe right now similarly or we can just
1:41
simply change the name let's say Michael
1:44
T and hit enter and we have the pending
1:47
fee for Michael so this is the basic use
1:51
of X lookup function let's take another
1:54
quick example for our practice now for
1:57
the next example I will click here on
1:59
example to wild card sheet and here we
2:02
have the exact same data of student name
2:05
pending fee and we want to find out
2:07
pending fee but there is a catch here we
2:08
want something specific instead of
2:10
writing the full student name for
2:12
example here we have Michael T so
2:14
instead of writing here Michael T I just
2:17
want to write the first name Michael and
2:18
I want the pending fee for the same
2:21
similarly if I want to write uh Joe rers
2:24
no I want to write just Joe so if I
2:26
write just Joe I want the pending fee of
2:28
Joe to be displ here so we are going to
2:31
use the X lookup function here and to
2:33
write that function we'll simply say
2:35
equals and then X lookup open
2:39
parenthesis now is it is the lookup
2:42
value what are we looking for we are
2:44
looking for this student so I'll click
2:45
on it but this is just the first name so
2:48
we are going to append wild card here so
2:51
it's something like ERS and then in
2:53
quotation marks I will write a star all
2:56
right so this means I'm looking for this
2:59
name this is the the first name and
3:01
whatever comes after it which means this
3:03
wild card just ignore that we only look
3:07
we are only looking for the first name
3:09
after this is the comma then we have the
3:12
lookup array where is this from of
3:14
course this is from this column of the
3:16
student names then we have another comma
3:19
all right now what is the return array
3:21
we are looking for the pending fees so
3:23
select this column of pending fees and
3:25
then we have a comma then we have if not
3:28
found we don't need this so I'm going to
3:30
skip it by writing another comma then we
3:33
have match mode this is this is really
3:35
important for wild card as you can see
3:37
it says zero for exact match one match
3:40
uh one for exact match or next smaller
3:42
item then we have another one exact
3:44
match or next larger item and then we
3:46
have this wild card character match
3:49
which is denoted by two so in order to
3:51
use the Wild Card feature of X lookup we
3:54
need to write here two and once we write
3:56
two just close the parenthesis and hit
3:59
enter and as you can see we have the
4:01
pending fee for Joe which is $2,000 now
4:05
let's uh write for Bob so I'm just going
4:07
to write the first name I'll write Bob
4:10
and hit enter and we have the pending
4:13
fee for Bob now before ending the video
4:16
Let's Take a more little complex problem
4:19
so here I'm going to click on this sheet
4:20
example three multiple conditions now
4:23
here we have a little bit modified data
4:26
we have the same student names but we
4:28
have pending fee for different months
4:30
this is for January this is for Feb and
4:33
this is for March now here I want to
4:36
basically find the pending fee based on
4:38
the month right so let's say student
4:40
name is Alice and for Alice I want to
4:42
find out the pending fee for March right
4:46
so we have multiple conditions here we
4:48
have a student name and then we have a
4:50
month also so here we are going to use x
4:53
lookup again so here in this cell I will
4:55
write equals x lookup and then
5:00
parenthesis now what are we looking for
5:02
we are looking for this value this
5:04
student right so I'll click on it and
5:06
then uh comma Now what is the lookup
5:09
array which means where does this value
5:11
actually come from so it comes from this
5:14
student name column so we'll select this
5:16
one and then comma Now the return array
5:19
is what we exactly want right but here
5:22
we have another criteria of month right
5:25
so instead of writing a return array I'm
5:27
going to write another function inside
5:29
which is going to be X lookup again so
5:31
here I'll type X lookup again and then
5:35
open parenthesis and now I it will ask
5:38
for the look up value now in this case
5:41
I'm looking for this month which is here
5:43
it's not visible because of this
5:44
textbookx but it is in G four so I'll
5:48
write G 4 all right which is the month
5:51
and then comma then we have the look up
5:54
array now where does this month come
5:56
from of course here are the months so we
5:59
are looking for this lookup array and
6:00
then we have another comma then we have
6:03
the return array all right what do we
6:05
want we want of course the pending fee
6:08
so I'm going to select three of these
6:10
columns that have all the pending fee
6:13
data and then I'm just simply going to
6:15
close the parenthesis for the first
6:17
xlookup function and then for the second
6:21
X lookup function as well and once I hit
6:24
enter as you can see we have the pending
6:26
fee for Alice for the month of March
6:29
which is $4,400 if we look here Alice
6:33
$4,400 similarly if I uh look for some
6:37
other student let's say I am looking for
6:40
Michael T uh for March it says $2,000
6:43
and it's exactly the same let's say uh
6:48
January and it is exactly $88,000 as it
6:52
is written here now you need to
6:55
understand that X lookup can be a little
6:57
confusing if you are a beginner so the
6:59
best way to master it is to practice it
7:03
I am going to create another video in
7:04
which we will take a couple of more
7:06
examples on how to use the X lookup
7:08
function also don't forget to download
7:11
this practice sheet from the link in the
7:13
description below thanks for watching be
7:15
sure to like this video share it with
7:17
your friends and I will see you in the
