The most anticipated new feature in Excel for 2019 is here - XLOOKUP. I'll
show you why.
If you've used VLOOKUP, HLOOKUP, or a combination of INDEX/MATCH get ready
to replace those all with XLOOKUP. For example let's take a look at XLOOKUP
with this value, in this list. Now notice I'm not selecting the entire range of
data anymore. I'm just picking the array that I'm looking the data up, and we
want to return the value from this March column so instead of specifying how many
columns over I'm actually highlighting the columns and that's all you have to
do for the XLOOKUP function to find Sally on this row and return the March
value of 22,454. Now one of the nice features of XLOOKUP is it defaults to
an exact match so if you come back here the next parameter is exact match but
you can also specify exact or the next smaller item exact or the next larger
item or do a wild-card character match like the INDEX/MATCH function. So let's
take a look at the horizontal search like HLOOKUP.
Let's do an XLOOKUP of March. This time we're going to pick this row of data and
we want it to return the row for Sally and you can see that it also returned
the 22,454 because it found March and went down to the row for Sally. Now one
of the other parameters for XLOOKUP. Let's say we are looking up 46% in this
list and we want to return the January data. Notice it doesn't find 46 in the
list so it returns an #N/A but let's put in there the exact match or next smaller
item. 45 is the next lower item and it
returns 22,793 from the January column. Let's do the same thing but this time
we're gonna pick the exact match or next larger item and the next larger is 50 so
it returns this value. Another great feature of XLOOKUP - let's say we are
looking up Sally in this list but instead of returning just the March
column let's do the entire range. See what it did? It returned the values for
the entire range that I selected for just Sally - 22,793 through 26,992. Now
let's do a wild-card search but this time we're gonna put in there J??
in this list and we're gonna return the March column
again. This time we're gonna pick a wild-card character match. This is
going to look for J with two letters following it and return the 31,097 which
is Jim for March. I can do the same thing with an asterisk and it finds the first
J with anything following it. Now another cool feature is the next parameter which
allows you to search first to last by default but you can search last to first.
So let's find anything that starts with a J starting from the bottom of the list
backwards and notice it returned 31,298 which is this value here because
that's the last one in the list starting with a J. You can even combine XLOOKUP
functions to be even more powerful. So let's do an XLOOKUP of Sally in this
list and we will return the data for March and we have our 22,454
which is this value right here but this time let's take this second
array and replace it with another XLOOKUP function and we're going to look
up this value in this set of data and we're gonna return the entire array of
data. End parentheses and you can see that it returned 22,454 which is the
intersection of Sally and March. So now if I change these values to February it
returns 28,391 which is the intersection of Sally and February. Change this to
John and you return 17,232. And that's the power of the XLOOKUP function. You
should receive this in the winter 2019 release of any Excel updates and you
should be able to use this to replace VLOOKUP, HLOOKUP, and INDEX/MATCH
combinations from this point forward. Enjoy! Hey, if you want to see more videos
like this one, please subscribe and if you've enjoyed this video be sure to
click the thumbs-up and leave a comment. I really do appreciate your support!