Up next in 10
Excel XLOOKUP Function - Perform Advanced Lookups using Match and Search Modes
8K views
Nov 7, 2024
In this video, I’ll guide you through the XLOOKUP function—Excel's powerful tool for advanced data lookups. You’ll learn how to perform exact and approximate lookups, search both vertically and horizontally, and even use wildcard characters. Plus, I'll explain how to apply match modes like exact or next larger value and show you how to control search order with search mode. 00:00 Introducing the XLOOKUP Function 01:38 How to Perform Basic Vertical Lookups 04:00 Horizontal Lookups Explained 05:03 Match Mode: Using Approximate Lookups 05:47 Match Mode: Finding Exact or Next Largest Value 07:55 Match Mode: Finding Exact or Next Smaller Value 11:01 Match Mode: Using Wildcard Characters 12:18 Introducing the Search Mode 12:46 Search Mode: Reversing the Search Order
View Video Transcript
0:00
hey everyone welcome back to the channel
0:02
in today's video we're diving into the
0:04
powerful X lookup function the X lookup
0:07
function can search in any direction
0:09
left right above or below it's a more
0:12
advanced alternative to lookup V lookup
0:15
and hookup and it's available in Excel
0:17
365 and Excel 2021 let's talk about the
0:21
syntax don't worry it's simpler than it
0:23
looks first we have the lookup value
0:26
this is the value you're trying to find
0:28
it could be a number some text or a
0:31
reference to a cell second is the lookup
0:33
array this is the range where Excel will
0:35
search for the lookup value third we
0:38
have the return array this is the range
0:40
where Excel will pull the corresponding
0:42
value once it finds a match fourth is
0:44
the if not found argument this one's
0:47
optional but very useful if Excel
0:49
doesn't find a match instead of showing
0:51
an error like na you can specify a
0:54
custom message like not found it keeps
0:56
things cleaner and more userfriendly
0:58
fifth we have match mode which is also
1:01
optional this tells Excel how it should
1:03
match the lookup value by default X
1:06
lookup looks for an exact match and this
1:08
is represented by zero if you want an
1:10
approximate match you can set it to
1:12
minus one for an exact match or the next
1:14
smaller value or to one for an exact
1:16
match or the next larger value there's
1:18
even an option to use wild cards with
1:20
two and finally there's search mode this
1:23
argument tells Excel the order to search
1:25
in the default setting is one meaning
1:27
Excel searches from top to bottom or
1:29
left left to right the two and minus two
1:31
settings are used to perform a binary
1:33
search which is faster but requires your
1:36
data to be sorted either in ascending or
1:38
descending order with this function you
1:40
can search for a value in one range and
1:42
easily retrieve a corresponding value
1:44
from another range all while enjoying
1:46
greater flexibility and fewer
1:48
restrictions let's take a look at a
1:50
simple example to see X look up in
1:52
action here we have a table listing
1:54
students and their scores now let's find
1:57
Alice Johnson's score we'll use the xook
2:00
formula in the cell where we want the
2:01
result as you can see when we enter the
2:04
formula Excel returns 85 easy right now
2:08
let's look up David Brown's score just
2:10
like that Excel returns 88 which is
2:12
exactly what we expected next let's
2:15
search for a student name that isn't in
2:16
our table when I try to look up ABC
2:19
Excel gives us the na error this means
2:22
the name wasn't found in the lookup
2:23
array now it's worth noting that unlike
2:26
other lookup functions like V lookup the
2:28
X lookup function defaults to an exact
2:30
match so if the exact value isn't found
2:33
you'll get that in a error unless you
2:35
change the match mode but here's where X
2:37
lookup shines even more it includes an
2:40
optional argument to handle cases where
2:42
no match is found instead of seeing that
2:44
frustrating in a error you can display a
2:47
custom message for instance if I search
2:50
for ABC which isn't in our table I can
2:52
add not found as the fourth parameter in
2:54
the formula now Excel will return not
2:57
found instead of the error the previous
3:00
example demonstrated a left to right
3:01
lookup where we search for a student's
3:03
score based on their name now let's flip
3:06
the direction and perform a right to
3:08
left lookup this time we'll use the X
3:11
lookup function to find a student's name
3:13
based on their score in this example 92
3:16
is the score we're looking for in the
3:17
range B2 to B15 and we want to return
3:20
the corresponding student name from the
3:22
range A2 to a15 the formula will return
3:25
Bob Smith now let's try finding another
3:27
Student's name based on a score that
3:29
doesn't exist in the score range when
3:32
this happens the result will be in a
3:34
error this is because xlookup by default
3:37
looks for an exact match and na
3:39
indicates that no exact match was found
3:42
to handle this we can use the fourth
3:44
argument if not found to customize the
3:46
error message instead of showing na we
3:49
can display something like not found
3:52
this is a great feature allowing you to
3:54
customize error handling or even perform
3:56
alternative actions when a lookup fails
3:59
now
4:00
let's take things a step further one of
4:02
the key advantages of xlookup over
4:04
traditional vlookup and hookup is its
4:06
ability to search both vertically and
4:08
horizontally to demonstrate this we'll
4:10
switch to a horizontal table layout
4:13
we'll use the same data but this time
4:15
arranged in columns the students names
4:17
are now in row one spread across columns
4:19
B to O in the range B1 to1 while their
4:22
scores are in row two spread across the
4:24
same columns in the range B2 to2 in this
4:27
example we're looking up the score for
4:29
Bob Smith in row one and returning the
4:31
corresponding score from row two as you
4:33
can see the formula returns 92 which is
4:36
Bob Smith's score demonstrating X
4:38
lookups versatility and handling
4:40
horizontal lookups similarly you can
4:42
find a student's name based on a given
4:44
score to do this we use the formula as
4:47
shown on the screen in this case the
4:49
formula searches for the score 92 in row
4:52
two and Returns the corresponding
4:53
students name from Row one the result is
4:56
Bob Smith the student with a score of 92
5:01
now let's dive into X lookups fifth
5:04
argument match mode we'll demonstrate
5:06
this by generating student grades the
5:08
match mode argument can have one of four
5:10
values 0 minus one 1 or two zero means
5:15
exact match minus one means exact match
5:18
or the next smaller value one means
5:21
exact match or the next larger value two
5:23
allows matching Wild Card characters
5:25
like asterisk and question mark this
5:27
argument is optional so if you don't
5:29
provide a value X lookup defaults to
5:32
zero which looks for an exact match all
5:34
of our previous examples have used this
5:36
default exact match setting to
5:39
demonstrate how the match mode argument
5:40
works we'll use the value one which
5:43
finds the next larger value if the exact
5:45
lookup value isn't available in the
5:47
lookup table in the lookup table we've
5:49
broken down the scores into 100 90 80 70
5:53
60 and 50 with their corresponding
5:55
grades A B C D E and F the logic here is
6:01
simple if a user score is between 91 and
6:04
100 they get a if it's between 81 and 90
6:07
they get B and so on scores 50 or below
6:11
will get F to generate the grade for the
6:13
first student write this formula in cell
6:15
C2 as shown on the screen the formula
6:18
generates B for the score of 85 which is
6:21
exactly what we were expecting let's
6:22
break this down the first argument B2 is
6:26
the user score that we want to match the
6:28
second argument is the range for our
6:29
break points the third argument is the
6:31
range for the grades we want to return
6:33
notice I've used the dollar signs to
6:35
lock the ranges so that when we copy
6:37
this formula down these ranges remain
6:39
fixed we haven't used the fourth
6:41
argument if not found because it's not
6:43
needed in this case since it's optional
6:45
we can skip it to do so simply leave it
6:48
blank by adding a comma and moving on to
6:50
the next argument the value one in the
6:52
fifth argument tells X lookup to find
6:54
the next larger value if an exact match
6:57
isn't found for example if a student has
6:59
a score of 85 x lookup will search the
7:02
lookup table for a match since there's
7:04
no exact match for 85 the function will
7:07
find the next larger value which is 90
7:10
associated with the Grade B so for a
7:12
score of 85 the function will return B
7:15
the same logic applies to a score of 92
7:18
since it's not available in the lookup
7:19
table X lookup will find the next larger
7:22
value which is 100 as 100 corresponds to
7:25
grade a the function will return a
7:28
here's another key difference between
7:29
lukup and the traditional lookup or
7:31
vlookup functions zuk cup doesn't
7:33
require the data to be sorted in
7:35
ascending order when performing in
7:36
approximate match it can find the
7:38
correct match regardless of the order in
7:40
the lookup table as you can see on the
7:42
screen I've rearranged the break points
7:44
and they're corresponding grades
7:46
randomly but the lcup formulas in the
7:48
grades column still return the correct
7:55
grades now let's explore another option
7:59
search mode with the value minus one
8:01
which tells X lookup to search for the
8:02
next smaller value in the lookup range
8:04
if an exact match isn't found we'll
8:06
demonstrate this by calculating the
8:08
discount for each customer based on
8:10
their spending the more they spend the
8:12
higher the discount percentage they
8:13
receive if a customer spends a minimum
8:16
of $200 they receive a 5% discount for a
8:19
minimum of $500 the discount increases
8:22
to 10% and for customer spending $1,000
8:25
or more the discount is 20% I've created
8:28
a lookup table with these thresholds the
8:31
spin thresholds are in H3 to H5 and the
8:34
corresponding discounts are in I3 to I5
8:37
to calculate the discount we've used
8:39
this formula let's break down this
8:41
formula B2 is the lookup value which is
8:44
the customer spending in this case H3 H5
8:47
is the lookup array containing the spend
8:49
thresholds I3 I5 is the return array
8:53
which holds the corresponding discount
8:55
percentages the fourth argument is left
8:57
blank meaning we're not using the
8:59
optional if not found argument the fifth
9:01
argument minus one in match mode tells X
9:05
look up to find an exact match or if
9:07
that isn't available the next smaller
9:09
value in our case this is perfect
9:11
because we want to apply the highest
9:13
possible discount without exceeding the
9:15
customer spending for example if a
9:17
customer spends
9:19
$312 there's no exact match for $312 in
9:22
the lookup table but because we've used
9:24
minus one X lookup will find the next
9:27
smaller value which is $200 and a the 5%
9:30
discount now let's double click the fill
9:32
handle or drag it down to calculate the
9:34
discount for all customers but as you
9:37
can see the formula generates a value
9:39
error let's edit our formula in cell C2
9:42
the issue is that we haven't used the
9:44
dollar sign in the ranges for the lookup
9:46
array and the return array which is why
9:48
we're seeing the error to fix this we
9:50
need to use absolute references by
9:52
adding the dollar sign to both ranges so
9:54
they continue to point to the correct
9:56
lookup table as we copy the formula down
9:58
notice that our formula gives na error
10:01
if the spending is less than $200 this
10:04
happens because xlookup can't find a
10:06
match for values below our lowest
10:08
threshold which is $200 in the lookup
10:10
table since there's no lower value to
10:13
return it results in an error to fix
10:15
this we can use the if not found
10:17
argument to provide a custom message or
10:19
value when no match is found for example
10:21
we could modify the formula like shown
10:23
on the screen this way if the spending
10:26
is less than $200 the formula will
10:28
return0 0 instead of giving an error you
10:31
might also notice that the formula
10:32
returns values like
10:34
0.05 0.1 and 0.2 instead of showing as
10:38
percentages this happens because Excel
10:41
interprets the numbers as decimals to
10:43
fix this simply format the cells as
10:46
percentages select the discount column
10:48
right click choose format cells and then
10:50
select percentage now the results will
10:53
display correctly as 5% 10% and 20%
11:00
next let's talk about wild card
11:02
characters in this lukup function wild
11:04
cards allow you to perform flexible
11:06
lookups when you're not sure of the
11:08
exact value zlookup supports two types
11:10
of wild cards the asterisk which
11:13
represents any number of characters and
11:15
the question mark which represents a
11:17
single character for example if you're
11:19
looking for a customer whose name starts
11:21
with B but you don't remember the full
11:22
name you could use this formula here the
11:25
asterisk after the B tells Excel to look
11:28
for any name start with b no matter what
11:31
follows so if there's a customer named
11:33
Bob or bush the formula will return
11:36
their complete name the asterisk can
11:38
replace any combination of characters
11:40
let's take another example this time
11:42
we'll use the question mark say you're
11:44
looking for a name that has exactly
11:45
three characters starts with an e and
11:48
ends with an a you can use the question
11:50
mark as a placeholder for any single
11:52
character in the middle in this case the
11:54
E and A are fixed while the question
11:56
mark represents any single character
11:58
between them so if a name like Evo
12:01
exists in the range it will be returned
12:03
by the formula this wild card technique
12:05
is very useful when you know part of the
12:07
data but need to fill in
12:14
gaps now let's discuss the search mode
12:17
argument in xlookup which controls the
12:20
search order in your data there are four
12:21
options for search mode the first option
12:24
is one which is the default setting that
12:26
searches from top to bottom or left to
12:28
right the second option minus one
12:30
searches in reverse order from bottom to
12:33
top or right to left the third option
12:36
two performs a binary search on data
12:38
sorted in ascending order finally the
12:40
fourth option minus two performs a
12:43
binary search on data sorted in
12:45
descending order we have two customers
12:47
with the same name Eva white in our
12:49
table one Eva white is in cell A6 with a
12:52
spending of
12:53
$1,820 in B6 and the second Eva white is
12:56
in cell a16 with a spending of $392 and
13:00
be16 by default X look up searches from
13:03
the top of the list so if we look for
13:05
Eva white without specifying of search
13:07
mode it will return the data for the
13:09
first Eva white it finds which is the
13:11
one in A6 with $18 $20 spending but what
13:14
if we want to find the second Eva white
13:16
the one in a16 with
13:18
$392 this is where the search mode
13:20
argument becomes useful to search from
13:22
the bottom to the top we'll use minus
13:24
one in the search mode argument in this
13:27
formula the minus one tells C cell to
13:29
search in reverse order starting from
13:31
the bottom this way the formula will
13:33
return the data for the second Evo white
13:35
with a spending of $392 in B16 so search
13:40
mode gives you the flexibility to
13:42
control the direction of your search
13:43
which is especially useful when you have
13:45
duplicate names or need to find the most
13:47
recent entry in your data thank you for
13:49
watching please share this video and
13:51
don't forget to like subscribe and hit
13:53
the notification Bell for more Excel
13:55
tips and tutorials your support helps us
13:57
create more valuable content see you in
13:59
the next video