Here is a look at the final version of XLOOKUP, along with 10 benefits of XLOOKUP over VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH.
Table of Contents
(0:00 Welcome and Introduction to Excel XLOOKUP Deep Dive
(0:25) List of Benefits
(2:30) First XLOOKUP
(3:02) 3rd argument is not an integer
(3:29) Handle items not found
(3:51) Range lookup find value just less like VLOOKUP(,,,True)
(4:41) Find value just larger like MATCH - without sorting
(5:16) HLOOKUP replaced by XLOOKUP
(5:30) Search from bottom of table
(6:21) Wildcards in XLOOKUP versus VLOOKUP
(6:56) Return all 12 months from table with single formula
(7:38) XLOOKUP can return a cell reference XLOOKUP:XLOOKUP
(9:10) Two-Way Match
(10:09) Sum All lookups
(10:44) Twisted XLOOKUP
(11:13) Wrap up on XLOOKUP
(11:30) Swag at Ignite Conference
Table of Contents
(0:00 Welcome and Introduction to Excel XLOOKUP Deep Dive
(0:25) List of Benefits
(2:30) First XLOOKUP
(3:02) 3rd argument is not an integer
(3:29) Handle items not found
(3:51) Range lookup find value just less like VLOOKUP(,,,True)
(4:41) Find value just larger like MATCH - without sorting
(5:16) HLOOKUP replaced by XLOOKUP
(5:30) Search from bottom of table
(6:21) Wildcards in XLOOKUP versus VLOOKUP
(6:56) Return all 12 months from table with single formula
(7:38) XLOOKUP can return a cell reference XLOOKUP:XLOOKUP
(9:10) Two-Way Match
(10:09) Sum All lookups
(10:44) Twisted XLOOKUP
(11:13) Wrap up on XLOOKUP
(11:30) Swag at Ignite Conference
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2291. XLOOKUP Version 2.
Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen and we're going to take a look at a comprehensive look today at XLOOKUP - the new version of XLOOKUP.
Yes, there was a video two months ago, but things have changed.
They've made some improvements so let's just go through all of the benefits XLOOKUP is designed to unite the VLOOKUP or INDEX & MATCH camps or HLOOKUP or LOOKUP. Whatever you're looking using.
This has it for you and there's several benefits.
Number one Match_Mode defaults to an exact match.
No more need to end your VLOOKUPs with Comma False. Or with Comma Zero.
Number 2: the integer based column number that was in VLOOKUP the third argument: gone, replaced by a proper cell reference, which means that you will not break VLOOKUP if one of your coworkers insert a column in the table.
Number 3: IFNA functionality is built in don't have to wrap the VLOOKUP in IFNA(). You just use the If_Not_Found new 4th argument.
Benefit #4: search for the value equal to or just larger.
Only MATCH could do that before.
Or equal to or just smaller and neither of them could do it without sorting the table.
Now we don't have to sort the table. Benefit number 5. XLOOKUP does HLOOKUP.
Benefit number six: This is a cool one.
It can search from the top of the table like VLOOKUP or from the bottom of the table.
to find the last match. There's uses for that.
Ahh, Benefit #7: you don't even realize this was a problem, but trust me, it's been a problem VLOOKUP has been treating asterisks question marks and tildes as wildcards.
You might've been getting the wrong answer.
You didn't realize they were treating those as wildcards. That's turned off now. You can turn it back on.
Benefit number eight. This is the one I love.
XLOOKUP will return all 12 months from the table with a single formula.
This is the time when I would switch from VLOOKUP to the INDEX and MATCH with one MATCH and 12 INDEX.
Now it's just one XLOOKUP. Does it all. This benefit #9 very very subtle.
Excel can return a cell reference which allows me to do a construct like XLOOKUP Colon XLOOKUP cell reference cell reference that I can look at SUM a variable range, let's say.
And then yeah, we can do a two-way match like INDEX(,MATCH,MATCH did.
I can sum all like the old old look up function used to do and if we use TRANSPOSE it can even do the twisted look up.
Alright, so let's dive in and see what we can do here.
First thing XLOOKUP.
So I want to find the XLOOKUP of W25-6.
Where am I going to look? I'm going to look here in column L.
I'll press F4 because I'm going to copy this down to lock that reference down.
This is where I would normally say 3 because I wanted the third item from the table.
But now I'm going to specify exactly what I want. I want the price.
Again f4 there and then that's it.
I don't need to do if_not_found, match_mode, search_mode, it just automatically defaults to exact match, which is great. That's what we do most of the time, so that's.
Benefit number 1.
Benefit number 2: What if your coworker comes along and decides that they need to insert a column here?
Well, that would have broken VLOOKUP because it would start returning the wrong column, but check this out because we were specifying a cell reference.
The cell reference moves over.
Alright, so no more having your VLOOKUPs broken because some crazy coworker deleted or inserted a column in your table.
Alright handle IFNA is the 4th argument.
I took a bunch of items here and replaced it with an item that's not on the table.
And of course we get the #N/A! there. The Not Available error. New 4th argument here.
We can gently replace that with "Not Found" or 0 or whatever and not have to wrap the VLOOKUP in IFNA. That's cool right there.
Alright the other version of VLOOKUP.
Not the common false, but the comma true. It would find the value just less.
So we're looking for 12,835 in this table.
It would return a bonus of 12.
If we're looking for something really close to 20,000 but not over 20,000, still going to return the 12, we have to get above 20,000 before we turn the last item.
So how do we do that in our XLOOKUP?
Look at the match_mode, we say We want the exact match or the next smaller item by using negative one.
Now in VLOOKUP, that would have been comma true, which is equal to a 1.
And in MATCH, that would have been one right?
So they reversed the one and the minus one.
But this one actually makes more sense, so I'm going to live with that without complaining about it at all.
What if we wanted to find the value just larger?
VLOOKUP could not do that. MATCH could.
But now XLOOKUP has no problem with it. Here is the match type.
We're saying we're looking for the value that is an exact match or the next larger item.
So if we have two people, a car is fine. Double click and copy that down.
But here for 11 people we need the Tour Van with 12.
And, oh by the way, in VLOOKUP comma True or MATCH comma 1 or -1, this table had to be sorted. No longer has to be sorted. It can be in.
any order and it continues to work.
HLOOKUP for someone who built the table going the wrong way.
No problem there, so we're looking for the month name there and returning the rate from there. HLOOKUP is no problem with XLOOKUP.
All right, next up. This one is cool. This one is cool.
There was an old old episode of MrExcel Netcast where I solve this problem for a horse farm in England and they were so happy with the complicated formula I created.
They actually named one of their race horses after MrExcel.
What they were trying to do is they had a series of vehicles every time the vehicle comes in for gas, they would log the mileage and they wanted to find the last match so when we look for red Ford truck, I don't want 88020.
I want 83,250.
We are looking from the bottom of the list. How do we do that?
The last argument here is minus one for search last-to-first.
Alright, so let's take a look at the formula.
It's called search mode and we say search last to first and it will find it the last one in the list. Very cool.
Alright, Wildcards.
people didn't realize this, but when you did a VLOOKUP, we look up and you were searching for Wal-star-Mart, it's going to return the first thing that starts with wal and ends with mart.
There's the 30 which is technically wrong.
XLOOKUP by default will not acknowledge wildcards.
If you need it to return Wallboard Mart, then you specify out here comma 2 for a wild card character match so it can still do it, but it's not going to do it unless you specifically ask for it, which is great.
All right. Here is my favorite one.
One formula returns all 12 months when I had to do a VLOOKUP and I had it repeated for January, February March, April, May, June.
It was a real hassle because that third argument had to be changed from comma-two to comma-three.
And then to comma-4, Comma-5, Comma-6, Comma-7 or hide a row here with helper cells with two through 12 or use the column function. All of those were weird.
Now, one formula we are looking up A6 in this list of account numbers.
What do we want to return?
We want to return the whole array like that? Press enter.
1 formula gets the job done absolutely amazing.
I love that one. Alright, this one is super super obscure.
There were nine functions.
Actually, there were eight but now there is nine functions that have a very weird behavior in Excel.
Normally they return a value, but it's possible for these to return a cell reference.
The reference is triggered when there's a colon next to the function or a space for an intersection operator or union operator, right? So here, take a look at this.
We're looking up Fig over here in this list and what we're getting is 6.
So XLOOKUP very simple little function here to return the six.
And then I'm looking up Orange and it's returning 15. Alright.
Simple enough, but if in this formula I say I want to do the sum of the XLOOKUP which should be 6, Colon and then the other XLOOKUP up which should be 15.
What it is actually doing is it's going to evaluate this and not return the six. It's going to return the answer of B9.
It's going to tell me where that's found.
Crazy, this works. Formula, Evaluate Formula.
So we go Evaluate right here. Here's where it should return the 6.
But because it's next to a colon, it returns B9.
I'd like that and then this one, it should return the number 15.
It returns B18, right?
So now we're summing everything from here to here and getting the total of those.
I can see all kinds of uses for this.
If you're trying to add up everything you know from a certain range of values, find the first one, find the last one.
Everything in between very subtle, but Excel Tricksters will love that alright.
Speaking of Excel Tricksters Index and Match with two matches to be able to do a two-way look up, we want to find the intersection of A621 and May.
So INDEX MATCH MATCH was the typical way to do that.
This is a completely different approach.
We're going to do is we're looking for a 621 over here in this list, but then what's the results array?
The results array is going to be another XLOOKUP.
We're looking for May in this list and what do we want to return?
We want to return all of these values, which in fact returns like we return 12 months were returned the whole column.
So when we look up J6 in the in the months, it actually returns all of those answers. I get 90, 44, 110.
That becomes my results array which allows the XLOOKUP to return the 104.
This is going to take a little bit of time to wrap your head around, but definitely very cool.
Alright now just a few more things here that the old LOOKUP function could do.
An XLOOKUP can match those.
The LOOKUP function had the weird ability that you could say I want to look up all of these values and it would return all of the answers and then you can send it into the sum function so could total all of those bonuses.
XLOOKUP can do the same thing.
I want to look up all of these values. Here's my look up array.
Here's my results are a value of if not found is blank and then minus one for next smaller item.
I sent it into SUM function. We get the same answer.
Alright, the other oddity, the old old LOOKUP function could do a twisted look up where what we were looking for this letter C here and one of the corresponding value from something that went across so down and across.
It didn't have to be the same direction XLOOKUP natively doesn't support this, but you can simulate it by saying we're looking up C in this range and the results array is the TRANSPOSE of this.
range. That one is cheating just a tiny it.
The new XLOOKUP function? Lots of amazing amazing stuff here.
Trying to unite the VLOOKUP camp with the INDEX and MATCH camp with the HLOOKUP and with the LOOKUP. It can do it all.
Well, taking a look we got Ignite.
I'll be at Ignite all this week. Stop by and see me.
I'm recording a podcast Wednesday morning in the hub.
If you find me, I have some great swag to give you.
And then my last seminar Chattanooga, TN on November 12 2019. Catch me there and then that's it.
I'm only doing seminars inside of Florida.
Check out my new book MrExcel XL The Holy Grail of Excel tips. It doesn't cover XLOOKUP yet.
I will have to fix that, but it will cover everything else.
If you like these tips please.
subscribe and Ring that Bell.
Feel free to post any questions or comments in the comments below.
I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen and we're going to take a look at a comprehensive look today at XLOOKUP - the new version of XLOOKUP.
Yes, there was a video two months ago, but things have changed.
They've made some improvements so let's just go through all of the benefits XLOOKUP is designed to unite the VLOOKUP or INDEX & MATCH camps or HLOOKUP or LOOKUP. Whatever you're looking using.
This has it for you and there's several benefits.
Number one Match_Mode defaults to an exact match.
No more need to end your VLOOKUPs with Comma False. Or with Comma Zero.
Number 2: the integer based column number that was in VLOOKUP the third argument: gone, replaced by a proper cell reference, which means that you will not break VLOOKUP if one of your coworkers insert a column in the table.
Number 3: IFNA functionality is built in don't have to wrap the VLOOKUP in IFNA(). You just use the If_Not_Found new 4th argument.
Benefit #4: search for the value equal to or just larger.
Only MATCH could do that before.
Or equal to or just smaller and neither of them could do it without sorting the table.
Now we don't have to sort the table. Benefit number 5. XLOOKUP does HLOOKUP.
Benefit number six: This is a cool one.
It can search from the top of the table like VLOOKUP or from the bottom of the table.
to find the last match. There's uses for that.
Ahh, Benefit #7: you don't even realize this was a problem, but trust me, it's been a problem VLOOKUP has been treating asterisks question marks and tildes as wildcards.
You might've been getting the wrong answer.
You didn't realize they were treating those as wildcards. That's turned off now. You can turn it back on.
Benefit number eight. This is the one I love.
XLOOKUP will return all 12 months from the table with a single formula.
This is the time when I would switch from VLOOKUP to the INDEX and MATCH with one MATCH and 12 INDEX.
Now it's just one XLOOKUP. Does it all. This benefit #9 very very subtle.
Excel can return a cell reference which allows me to do a construct like XLOOKUP Colon XLOOKUP cell reference cell reference that I can look at SUM a variable range, let's say.
And then yeah, we can do a two-way match like INDEX(,MATCH,MATCH did.
I can sum all like the old old look up function used to do and if we use TRANSPOSE it can even do the twisted look up.
Alright, so let's dive in and see what we can do here.
First thing XLOOKUP.
So I want to find the XLOOKUP of W25-6.
Where am I going to look? I'm going to look here in column L.
I'll press F4 because I'm going to copy this down to lock that reference down.
This is where I would normally say 3 because I wanted the third item from the table.
But now I'm going to specify exactly what I want. I want the price.
Again f4 there and then that's it.
I don't need to do if_not_found, match_mode, search_mode, it just automatically defaults to exact match, which is great. That's what we do most of the time, so that's.
Benefit number 1.
Benefit number 2: What if your coworker comes along and decides that they need to insert a column here?
Well, that would have broken VLOOKUP because it would start returning the wrong column, but check this out because we were specifying a cell reference.
The cell reference moves over.
Alright, so no more having your VLOOKUPs broken because some crazy coworker deleted or inserted a column in your table.
Alright handle IFNA is the 4th argument.
I took a bunch of items here and replaced it with an item that's not on the table.
And of course we get the #N/A! there. The Not Available error. New 4th argument here.
We can gently replace that with "Not Found" or 0 or whatever and not have to wrap the VLOOKUP in IFNA. That's cool right there.
Alright the other version of VLOOKUP.
Not the common false, but the comma true. It would find the value just less.
So we're looking for 12,835 in this table.
It would return a bonus of 12.
If we're looking for something really close to 20,000 but not over 20,000, still going to return the 12, we have to get above 20,000 before we turn the last item.
So how do we do that in our XLOOKUP?
Look at the match_mode, we say We want the exact match or the next smaller item by using negative one.
Now in VLOOKUP, that would have been comma true, which is equal to a 1.
And in MATCH, that would have been one right?
So they reversed the one and the minus one.
But this one actually makes more sense, so I'm going to live with that without complaining about it at all.
What if we wanted to find the value just larger?
VLOOKUP could not do that. MATCH could.
But now XLOOKUP has no problem with it. Here is the match type.
We're saying we're looking for the value that is an exact match or the next larger item.
So if we have two people, a car is fine. Double click and copy that down.
But here for 11 people we need the Tour Van with 12.
And, oh by the way, in VLOOKUP comma True or MATCH comma 1 or -1, this table had to be sorted. No longer has to be sorted. It can be in.
any order and it continues to work.
HLOOKUP for someone who built the table going the wrong way.
No problem there, so we're looking for the month name there and returning the rate from there. HLOOKUP is no problem with XLOOKUP.
All right, next up. This one is cool. This one is cool.
There was an old old episode of MrExcel Netcast where I solve this problem for a horse farm in England and they were so happy with the complicated formula I created.
They actually named one of their race horses after MrExcel.
What they were trying to do is they had a series of vehicles every time the vehicle comes in for gas, they would log the mileage and they wanted to find the last match so when we look for red Ford truck, I don't want 88020.
I want 83,250.
We are looking from the bottom of the list. How do we do that?
The last argument here is minus one for search last-to-first.
Alright, so let's take a look at the formula.
It's called search mode and we say search last to first and it will find it the last one in the list. Very cool.
Alright, Wildcards.
people didn't realize this, but when you did a VLOOKUP, we look up and you were searching for Wal-star-Mart, it's going to return the first thing that starts with wal and ends with mart.
There's the 30 which is technically wrong.
XLOOKUP by default will not acknowledge wildcards.
If you need it to return Wallboard Mart, then you specify out here comma 2 for a wild card character match so it can still do it, but it's not going to do it unless you specifically ask for it, which is great.
All right. Here is my favorite one.
One formula returns all 12 months when I had to do a VLOOKUP and I had it repeated for January, February March, April, May, June.
It was a real hassle because that third argument had to be changed from comma-two to comma-three.
And then to comma-4, Comma-5, Comma-6, Comma-7 or hide a row here with helper cells with two through 12 or use the column function. All of those were weird.
Now, one formula we are looking up A6 in this list of account numbers.
What do we want to return?
We want to return the whole array like that? Press enter.
1 formula gets the job done absolutely amazing.
I love that one. Alright, this one is super super obscure.
There were nine functions.
Actually, there were eight but now there is nine functions that have a very weird behavior in Excel.
Normally they return a value, but it's possible for these to return a cell reference.
The reference is triggered when there's a colon next to the function or a space for an intersection operator or union operator, right? So here, take a look at this.
We're looking up Fig over here in this list and what we're getting is 6.
So XLOOKUP very simple little function here to return the six.
And then I'm looking up Orange and it's returning 15. Alright.
Simple enough, but if in this formula I say I want to do the sum of the XLOOKUP which should be 6, Colon and then the other XLOOKUP up which should be 15.
What it is actually doing is it's going to evaluate this and not return the six. It's going to return the answer of B9.
It's going to tell me where that's found.
Crazy, this works. Formula, Evaluate Formula.
So we go Evaluate right here. Here's where it should return the 6.
But because it's next to a colon, it returns B9.
I'd like that and then this one, it should return the number 15.
It returns B18, right?
So now we're summing everything from here to here and getting the total of those.
I can see all kinds of uses for this.
If you're trying to add up everything you know from a certain range of values, find the first one, find the last one.
Everything in between very subtle, but Excel Tricksters will love that alright.
Speaking of Excel Tricksters Index and Match with two matches to be able to do a two-way look up, we want to find the intersection of A621 and May.
So INDEX MATCH MATCH was the typical way to do that.
This is a completely different approach.
We're going to do is we're looking for a 621 over here in this list, but then what's the results array?
The results array is going to be another XLOOKUP.
We're looking for May in this list and what do we want to return?
We want to return all of these values, which in fact returns like we return 12 months were returned the whole column.
So when we look up J6 in the in the months, it actually returns all of those answers. I get 90, 44, 110.
That becomes my results array which allows the XLOOKUP to return the 104.
This is going to take a little bit of time to wrap your head around, but definitely very cool.
Alright now just a few more things here that the old LOOKUP function could do.
An XLOOKUP can match those.
The LOOKUP function had the weird ability that you could say I want to look up all of these values and it would return all of the answers and then you can send it into the sum function so could total all of those bonuses.
XLOOKUP can do the same thing.
I want to look up all of these values. Here's my look up array.
Here's my results are a value of if not found is blank and then minus one for next smaller item.
I sent it into SUM function. We get the same answer.
Alright, the other oddity, the old old LOOKUP function could do a twisted look up where what we were looking for this letter C here and one of the corresponding value from something that went across so down and across.
It didn't have to be the same direction XLOOKUP natively doesn't support this, but you can simulate it by saying we're looking up C in this range and the results array is the TRANSPOSE of this.
range. That one is cheating just a tiny it.
The new XLOOKUP function? Lots of amazing amazing stuff here.
Trying to unite the VLOOKUP camp with the INDEX and MATCH camp with the HLOOKUP and with the LOOKUP. It can do it all.
Well, taking a look we got Ignite.
I'll be at Ignite all this week. Stop by and see me.
I'm recording a podcast Wednesday morning in the hub.
If you find me, I have some great swag to give you.
And then my last seminar Chattanooga, TN on November 12 2019. Catch me there and then that's it.
I'm only doing seminars inside of Florida.
Check out my new book MrExcel XL The Holy Grail of Excel tips. It doesn't cover XLOOKUP yet.
I will have to fix that, but it will cover everything else.
If you like these tips please.
subscribe and Ring that Bell.
Feel free to post any questions or comments in the comments below.
I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel.
