Excel XLOOKUP will soon become the ONLY Excel LOOKUP Function you’ll ever need to solve any lookup problem you come across. If you’d like to learn about the other brand new Excel functions like FILTER \u0026 UNIQUE check out the complete course: https://courses.xelplus.com/p/excel-d…
In this video we cover 5 common Excel lookup problems:
1. Lookup returns an error (#NA) - This is a common problem with VLOOKUP. If the VLOOKUP function returns #NA it is likely because the item you’re looking up does not exist on the lookup table. The solution so far has been to wrap the VLOOKUP function inside IFNA or IFERROR functions. With XLOOKUP however, you no longer need to do that. You just need to use the 4th argument of XLOOKUP [If_not_found] optional argument and specify the value you’d like to get back if the value does not exist on the lookup array.
2. Horizontal lookup - In legacy Excel when we needed to lookup a value in a table that was structured horizontally instead of vertically we would use the HLOOKUP function. The new XLOOkUP function can do horizontal lookups as well. You just have to select the horizontal array instead of the vertical array.
3. Look for a partial match instead of an exact match in Excel: There might be times you need to look for a partial match. In this case you can combine wildcards such as the asterisk or question mark characters and combine them together with the 5th argument of XLOOKUP which is the optional [match mode] argument.
4. Two way Excel lookup: To look up a value on the rows as well as columns we generally combined VLOOKUP with Excel Match function or used the famous INDEX \u0026 MATCH functions. We no longer need to use two separate functions to do a two way lookup. We can use XLOOKUP inside another XLOOKUP to get the job done. The advantage is we just need to know one function well, instead of combining different Excel lookup functions with one another.
5. Excel approximate match lookup: There are times you’d like to look for a value inside a data table and return the closest match. This is specially used for cases where you have thresholds for example for bonus schemes. If a number falls between a specific threshold you’d like to return a corresponding value. This is where you can use XLOOKUP’s 5th argument [match mode] to look for “exact match or next smaller item” or “exact match or next larger item”.
00:00 What is Excel’s New XLOOKUP Function 01:15 Lookup Value Not Found with XLOOKUP 04:06 Horizontal Lookup with XLOOKUP in Excel 05:44 Look For Partial Match with XLOOKUP 07:51 Two-Way Lookup with XLOOKUP 10:29 Approximate Match with XLOOKUP
** AVAILABILITY ** The XLOOKUP function is available to Office 365 and 2021 users.
LINKS to related videos - First XLOOKUP video: • Discover What XLOOKUP Can Do For YOU … Excel left lookup with XLOOKUP: • Left Lookup in Excel with XLOOKUP (Ma…
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Content
0.24 -> Let's talk about Excel's
1.47 -> new and improved XLOOKUP function.
5.15 -> You might know by now that XLOOKUP
6.89 -> is Excel's new superhero lookup function
10.4 -> because it can replace VLOOKUP,
INDEX MATCH, and even more.
15.27 -> Today I'll take you through five examples
17.9 -> that will help you take advantage
19.71 -> of Excel's XLOOKUP function.
22.27 -> First example we're going to look at
23.73 -> is how to handle items not found,
26.61 -> so basically handling errors in XLOOKUP.
29.98 -> Then we're going to take a look at
31.07 -> using wildcards in the lookup,
33.93 -> so looking for partial
match instead of full match.
37.16 -> We're then going to do
a horizontal lookup.
39.89 -> We'll use XLOOKUP instead of HLOOKUP.
43.13 -> Then a two-way lookup,
44.73 -> so lookup on the rows and columns.
47.46 -> And finally how to look
48.86 -> for an approximate match with XLOOKUP.
52.09 -> Now I know a common question will be about
53.97 -> the availability of the XLOOKUP function.
56.92 -> Since that's changing,
58.48 -> check out the description of
the video to find out more,
61.72 -> so to see if you have it or if
you're going to get it soon.
65.65 -> Now let's get to our five examples.
68.061 -> (upbeat music)
71.84 -> Here we have a list of employee names
73.9 -> and their yearly salary.
75.56 -> Now what I want to do is to calculate
77.56 -> the maximum bonus each employee can have.
80.819 -> On a separate tab, B_Master,
83.76 -> I have the information on employee ID,
86.37 -> the bonus percentage,
and the employee name.
89.32 -> So in this case I don't have
any specific bonus scheme.
92.83 -> Each employee can have their
own specific bonus percentage.
97.76 -> What I want to do in the first step
99.5 -> is to bring over that bonus
percentage to this sheet.
103.256 -> Now here I just happen to
have the names, not the IDs.
106.72 -> I'll just do a lookup based on the names.
109.859 -> I'm going to start off with XLOOKUP.
111.97 -> So the moment you start typing it in,
114.1 -> you see it in the dropdown,
115.64 -> click tab to select it.
117.89 -> The lookup_value,
118.96 -> what are we looking for
for the employee here?
121.554 -> Next argument is the lookup_array,
124.26 -> where can I find this name?
126.23 -> Where is the complete list?
128.07 -> It's in the B_Master tab, it's right here.
131.69 -> Control + Shift + Down
to select the whole list.
134.82 -> Press F4 to fix it.
137.1 -> Next argument is the return_array,
138.64 -> what do I want to get back?
140.53 -> I want to get back the bonus percentages
143.41 -> so I'm going to select this.
145.06 -> Press F4.
146.52 -> And we're pretty much done here.
149.59 -> Notice that the other
arguments here are optional.
153.53 -> We don't need them right now.
154.56 -> We're going to take a
look at them in a second.
156.61 -> But the default match_mode for XLOOKUP
159.22 -> is to look for an exact match.
161.7 -> So we don't need to specify
anything else right now.
165.87 -> We can just press Enter and we're done.
168.84 -> Since we've done the
fixing correctly here,
171.53 -> let's just send this down.
173.8 -> These are the percentages
175.1 -> but we're getting some #N/As here.
178.24 -> How do we handle this?
180 -> The reason I get the errors here
182.35 -> is because these names
183.93 -> don't exist on the B_Master side.
187.191 -> Now here's what I can
do if I'm using XLOOKUP.
191.99 -> I can use the fourth argument,
194.715 -> if_not_found argument.
197.22 -> So what do I want to get back
198.95 -> if my lookup_value doesn't
exist in my lookup range?
204.47 -> In this specific case I want to give them
207.17 -> a zero percentage bonus.
209.28 -> So I'll just type in zero,
211.54 -> press Enter, and send this down.
214.89 -> Now I'm still not done with the formula,
217.08 -> what I want to do is take this percentage
220.04 -> and multiply it with the yearly salary
224.49 -> and send this down too.
227.12 -> Now ultimately you'll be creating
228.96 -> an Excel table out of this,
230.82 -> so you just have to
write your formula once
232.98 -> and it's going to copy it down.
234.73 -> Just to keep things simple
236.26 -> and to make it easier to follow,
238.3 -> I'm just using direct cell references.
240.956 -> So this is going to be our maximum bonus.
244.54 -> First task is done.
248.89 -> Now what do we do though if
the table that we receive
252.4 -> for the bonuses was arranged this way?
255.89 -> So we had to do a horizontal lookup
258.519 -> instead of a vertical lookup.
261.23 -> It's no problem for XLOOKUP.
263.52 -> Let's just try it here.
265.69 -> So we'll just have to do XLOOKUP.
268.053 -> Lookup_value, same value.
270.948 -> Where are we looking it up?
272.66 -> In B_Master.
274.15 -> Our employee names are right here,
276.23 -> so Control + Shift + and to the right
279.01 -> to select everything.
280.47 -> Press F4 to fix it.
282.139 -> Return_array is this one,
286.03 -> so Control + Shift + to the left,
287.98 -> and go one back up.
291.17 -> And let's fix these as well.
294.32 -> So remember default match is exact match.
297.38 -> That's it, press Enter,
and we have our values.
301.22 -> Now just to make sure
we have the same values,
303.57 -> let's just complete the formula
305.59 -> by multiplying this with this
308.15 -> and let's send this down.
311.53 -> Everything is the same.
313.07 -> How do we get rid of the #N/As?
315.38 -> Just like we did before,
317.02 -> let's use the fourth argument of XLOOKUP,
320.48 -> and put a zero there.
322.69 -> And we have the same
numbers here and here.
327.36 -> So that's how easy it is
to do a horizontal lookup.
331.88 -> And in both cases our lookup range
335.33 -> was after the range that
we want to get back,
339.45 -> and XLOOKUP has no problem handling this.
345.34 -> Now let's take a look at
how we can use wildcards
348.33 -> for our lookup_value.
350.69 -> So let's say we just remembered
353.13 -> the last name of our
employee called Willard,
356.89 -> and we want to find out
what his first name is.
360.47 -> Let's use XLOOKUP.
361.7 -> The lookup_value is Willard,
365.45 -> but because we're going to use wildcards,
368.33 -> we need to put the wildcard
as a part of our lookup_value.
372.995 -> So if we don't have it in a cell,
375.34 -> and in this case I
don't have it in a cell,
377.5 -> I have to type it in the formula,
379.7 -> but because the wildcard
character is considered text,
385.27 -> you have to put it in quotation mark.
387.34 -> And then you just have to
type in the asterisk sign
390.11 -> which is a placeholder for
text of variable length.
394.92 -> Add the quotation marks again,
396.91 -> and then connect this with
an & to the cell reference.
400.56 -> Now if you want to add a second wildcard
403.154 -> because this word could be
in the middle of the name,
407.16 -> you can follow this with this as well,
411.11 -> but in this case it's the last name,
414.12 -> nothing is going to come after this,
415.99 -> so we're good if we leave it in this way.
418.48 -> Lookup_array is right here.
421.42 -> Now I'm not going to fix it
422.37 -> because I'm not planning
to pull this down.
425.11 -> The return_array or in
this case is also here
428.33 -> because I want to find the full name.
430.7 -> So I'm just going to close
bracket, press Enter,
433.85 -> and it's not going to
work because I need to use
437.82 -> one of these optional arguments.
440.82 -> This is not the one I'm
looking formula, if_not_found.
443.377 -> Let's take a look at match_mode,
that's the one I need.
447.45 -> I'm not looking for an exact match.
449.71 -> So even if you use the
wildcard character in here,
453.01 -> you still need to go in
and adjust the match_mode
456.559 -> to accept wildcard character.
459.53 -> So don't forget this part.
461.25 -> Press Enter and then we
get the full name back.
464.58 -> So if I change this to Cooper for example,
468.83 -> I get Andrew Cooper back.
473.52 -> Now let's take a look at how
we can do a two-way lookup.
477.34 -> So I've just added maximum
payment to our list here
480.85 -> by adding the yearly salary
and the maximum bonus
483.66 -> each employee could get.
485.62 -> To make it simpler to select,
487.23 -> let's just quickly add
a data validation list.
490.08 -> So go to Data, Data Validation, List,
494.22 -> and for source, let's just
add the employees here,
498.37 -> and click on OK.
499.68 -> So here I can select an employee.
502.91 -> Right here let's add a
second data validation
506.17 -> where we can select one of these
fields here and go with OK.
512.33 -> Now right here I want to see the number
514.81 -> that's associated with James
Willard, maximum bonus.
519.77 -> I need to do a two-way lookup.
522.38 -> No problem.
523.55 -> Let's use XLOOKUP to do this.
526.13 -> First off, what's my lookup_value?
528.44 -> I'm looking for James Willard.
530.77 -> Next argument, what's the
range we're looking this up?
534.46 -> Right here.
535.384 -> Return_array.
537.35 -> Well, now our return_array
could be any of these, right?
542.34 -> Because it really depends
on what we've selected here.
546.17 -> We need to bring this one in.
548.12 -> I need to do a second XLOOKUP here
550.6 -> because I need to figure
out which of these arrays,
553.77 -> which of these ranges I
need to take for my lookup.
557.64 -> So here I'm going to do another XLOOKUP.
562.33 -> This time my lookup_value
is the maximum bonus.
566.54 -> Where am I looking this up?
568.98 -> In here.
570.26 -> What's the array I want back?
572.33 -> This array.
573.66 -> In all these cases we're
looking for an exact match,
576.542 -> so we don't have to worry
about the other arguments.
580.4 -> And that's it.
581.56 -> James Willard gets a
maximum bonus of zero.
584.81 -> That looks good.
586.01 -> Let's change this to
yearly salary, 39,627.
591 -> Let's just copy the formatting here.
593.226 -> Now let's change to Paul Garza.
597.15 -> Yearly salary, 34,000,
600.43 -> and maximum payment that we
have to pay him is 38,289.
607.579 -> Now here's the thing if I highlight this
610.43 -> and I press F9,
612.83 -> notice that we get an array back.
616.33 -> So our second lookup here
618.18 -> is returning the entire list
620.71 -> to our original lookup formula.
623.47 -> And that's another thing with XLOOKUP
625.43 -> is that you can do two-way lookups.
631.56 -> Now let's assume the company
632.88 -> just received a new bonus scheme.
635.422 -> The new bonus scheme is right here
637.99 -> and it's based on salary.
640.22 -> So anyone who earns up to
30,000, they get 0% bonus.
645.58 -> Between 30,000 to 50,000, they get 5%.
649.665 -> Above 100,000, they get 15% bonus.
653.7 -> That's the new scheme that
we want to apply to here.
657.04 -> So let's calculate the new maximum bonus
660.58 -> each employee can have.
662.83 -> So we're going to start off with XLOOKUP.
664.98 -> This time our lookup_value
is not the employee name
667.73 -> because we're looking at the numbers.
669.3 -> So we need this salary amount.
671.81 -> The lookup_array is where we have
673.98 -> our list of numbers which is right here.
676.768 -> I'm going to fix it because I'm planning
678.67 -> to copy the formula down.
681.04 -> The return_array, what
do I want to get back?
684.43 -> I want to get back these percentages.
686.534 -> I'm going to fix it as well.
688.562 -> Now I'm not done with the formula
690.89 -> because this is going to
default to an exact match.
694.04 -> I want to have an
approximate match instead.
697.337 -> I don't want to put anything
specific for these arguments.
700.12 -> I'm just going to skip it.
702 -> For match_mode now, I'm
going to go with minus one,
706.16 -> exact match or next smaller item.
710.35 -> Close bracket,
711.381 -> press Enter,
713.46 -> and that's 10% for Gary Miller.
715.98 -> So let's just take a look.
717.42 -> Gary Miller earns a
little bit over 60,000.
721.52 -> 60,000 is right here.
723.56 -> So he earns 10% because he
falls between these two values,
729.27 -> and he's taking the next
smallest item which is the 10%.
734.02 -> And anyone earning a salary over 100,000
737.7 -> should have a 15% bonus.
740.56 -> So let's just send this down,
741.82 -> see who's getting 15%.
744.25 -> Over 100, over 100, and also over 100,000.
749.38 -> Now based on this we have our percentages,
752.2 -> let's just calculate the maximum new bonus
755.54 -> each employee can have.
757.18 -> We're going to take this,
758.3 -> multiply it with this salary,
760.27 -> and make sure we change it to a number.
762.96 -> So let's just copy the
formatting to this one
765.926 -> and send this down.
768.26 -> Okay, so this is how you can do
769.84 -> an approximate match using XLOOKUP.
772.843 -> You have the ability in match_mode
775.509 -> to search for the exact match
777.94 -> or the next smaller item
779.57 -> or exact match or the next larger item.
783.49 -> I hope you enjoyed these
five different scenarios
785.9 -> of using Excel XLOOKUP.
788.13 -> If you like the XLOOKUP function,
790.3 -> give this video a thumbs up.
791.98 -> Let me know below if you think
793.34 -> you'll be using XLOOKUP
for your future files.
