The new XLOOKUP for Excel debuted yesterday. Can it replace INDEX-MATCH? Can it replace INDEX-MATCH-MATCH? The answer is Yes!
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2287.
The New XLOOKUP versus INDEX MATCH MATCH.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Yesterday in 2286 we talked about the new XLOOKUP function that came out and how much better was than VLOOKUP.
But then I got a lot of questions about hey well will it do INDEX and MATCH or more importantly INDEX MATCH MATCH?
The example here. As we have to do 12 columns of lookup.
So the typical solution is to add an extra column here that we're going to hide with a single match.
It figures out where this item is found in the list.
Once we know that, by the way, that MATCH takes as long as the first VLOOKUP the January VLOOKUP would've taken.
once we know that, then it's a simple little INDEX function and only lock down the rows that way as we copy that to the right.
It works, right?
This is the classic solution and the reason that I would switch over to INDEX and MATCH and the reason that a lot of people loved index and match.
But here is the beautiful thing. About our XLOOKUP.
It can return an array, so I'm looking at A308 in the red range over there an for my result I ask for the whole range and what this one formula amazingly is going to do.
is return all 12 months bam like that. How beautiful.
Once we get that formula and copy down and it's going to be so much easier than Index and Match.
But then they pushed back and said what about an INDEX MATCH MATCH to do a two-way look up?
and I'm going to answer that but I want to show you one amazing thing about XLOOKUP.
This list of functions right here.
These are the functions in Excel 1, 2, 3, 4, 5, 6, 7, 8 that can return a reference as well as a value.
That means if you put this function and have a colon right after it, or use an intersection operator or union operator, it will return a cell range reference instead of the value and let me show you what's working here.
I'm going to do a formula here that does XLOOKUP from Cherry to Kiwi and then send that whole thing into the SUM.
And amazingly that formula returns 2044, which is the sum of from here to here. Let's watch this happen and evaluate formula.
Alright so it starts out inside the first XLOOKUP. Evaluate.
and then it does the XLOOKUP and we're looking at cherry.
So you would think it's going to return the value 4, but it doesn't.
It returns the cell reference of B4 and then the second XLOOKUP.
We're looking up Kiwi.
So you would think it's going to return the 1024, but instead it returns B12, right? This is super cool.
One of those really, really obscure facts about Excel that allows people to create super formulas. All right now back to the problem.
The two way look up.
We have to look fine fig along the left hand side. Charlie along the top.
and classic solution INDEX of all of those cells here highlighted index of all of those cells. Which row do we want?
We want the MATCH of fig in this list.
Which column do we want? We want them match of Charlie and this list.
I have to tell you, Joe McDaid teased us.
He said there's a solution to this using XLOOKUP before I had XLOOKUP.
I tried to think of a solution, but couldn't figure it out.
I was always relying on offset, which is a volatile function and would be bad all right.
But here is the thing.
So we know we're going to do an X look up of Fig into this range and then we get to the what do you want to return?
How do I point to the right spot?
Check this out this formula down here says an excellent cup of Charlie into F2:I2. But what do we want to return?
We want to return the whole rectangular range and just like how XLOOKUP could return all the months January through December, going across this is going to return all of the cells going down, so when I'm looking for Charlie.
It gives me 24, 32, 40, 48, 56, ... right?
And if I would change this to look for Andy?
Then it returns 18, 24 30, 36..., right?
It's it's a beautiful thing returning all those values, but what I found is I'm going to take this whole formula right here that XLOOKUP.
Ctrl C and use that formula as the third argument to another XLOOKUP. So we have our first XLOOKUP. We're looking at B3.
We arere looking at fig in this list here and for the third argument, where do we want to find it?
It's going to be in that range.
This range right here. And when I evaluate formula.
So I'm looking at B3, that's fig.
Then it jumps over to the XLOOKUP. Find Charlie, it says and check this out.
What's going to happen when I click evaluate?
It's not going to give me 24. 32. 40. It's going to give me the reference.
That is beautiful and elegant, and the INDEX and MATCH people will love it because no one else will be able to figure out how it works. That seems to really be a big thing for them.
So awesome solution here.
If you simply hate this solution and you can't give up your INDEX MATCH MATCH, I'm going to ask you at least to switch over to the INDEX XMATCH XMATCH.
The beautiful thing about XMATCH is it defaults to comma zero, defaults to an exact match, so you don't need the third argument.
You don't need the comma zero there or the comma zero there.
XMATCH is a new function that came along yesterday along with XLOOKUP. Alright, so you have your old INDEX/MATCH/MATCH.
The new XLOOKUP/XLOOKUP, I call it the XX. Or the INDEX/XMATCH/XMATCH.
Three different ways to solve that problem.
All this is super exciting.
It went up to 50% of Office 365 Insiders Fast yesterday. I'm hoping that this rolls out.
I mean, it can't be that hard. It's not like dynamic arrays.
They should just roll this out.
Would be nice if it came out at Ignite, which happens in November.
My seminars left outside of Florida: Excelapalooza, Springfield, Chicago, Appleton, Chattanooga. Catch me out.
I also found out I will be presenting it Ignite which is in Orlando, FL.
That will be in early November, check out my book MrExcel, LX the Holy Grail of Excel.
Tips has everything except for these two new functions XLOOKUP and XMATCH and hey yeah, I guess I'm going to brag my video on YouTube was out without minutes after XLOOKUP came out yesterday.
If you want to hear the cool new stuff that hits Excel, click SUBSCRIBE and ring the Bell.
I want to thank you for stopping by.
I will see you next time for another netcast for MrExcel.
The New XLOOKUP versus INDEX MATCH MATCH.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Yesterday in 2286 we talked about the new XLOOKUP function that came out and how much better was than VLOOKUP.
But then I got a lot of questions about hey well will it do INDEX and MATCH or more importantly INDEX MATCH MATCH?
The example here. As we have to do 12 columns of lookup.
So the typical solution is to add an extra column here that we're going to hide with a single match.
It figures out where this item is found in the list.
Once we know that, by the way, that MATCH takes as long as the first VLOOKUP the January VLOOKUP would've taken.
once we know that, then it's a simple little INDEX function and only lock down the rows that way as we copy that to the right.
It works, right?
This is the classic solution and the reason that I would switch over to INDEX and MATCH and the reason that a lot of people loved index and match.
But here is the beautiful thing. About our XLOOKUP.
It can return an array, so I'm looking at A308 in the red range over there an for my result I ask for the whole range and what this one formula amazingly is going to do.
is return all 12 months bam like that. How beautiful.
Once we get that formula and copy down and it's going to be so much easier than Index and Match.
But then they pushed back and said what about an INDEX MATCH MATCH to do a two-way look up?
and I'm going to answer that but I want to show you one amazing thing about XLOOKUP.
This list of functions right here.
These are the functions in Excel 1, 2, 3, 4, 5, 6, 7, 8 that can return a reference as well as a value.
That means if you put this function and have a colon right after it, or use an intersection operator or union operator, it will return a cell range reference instead of the value and let me show you what's working here.
I'm going to do a formula here that does XLOOKUP from Cherry to Kiwi and then send that whole thing into the SUM.
And amazingly that formula returns 2044, which is the sum of from here to here. Let's watch this happen and evaluate formula.
Alright so it starts out inside the first XLOOKUP. Evaluate.
and then it does the XLOOKUP and we're looking at cherry.
So you would think it's going to return the value 4, but it doesn't.
It returns the cell reference of B4 and then the second XLOOKUP.
We're looking up Kiwi.
So you would think it's going to return the 1024, but instead it returns B12, right? This is super cool.
One of those really, really obscure facts about Excel that allows people to create super formulas. All right now back to the problem.
The two way look up.
We have to look fine fig along the left hand side. Charlie along the top.
and classic solution INDEX of all of those cells here highlighted index of all of those cells. Which row do we want?
We want the MATCH of fig in this list.
Which column do we want? We want them match of Charlie and this list.
I have to tell you, Joe McDaid teased us.
He said there's a solution to this using XLOOKUP before I had XLOOKUP.
I tried to think of a solution, but couldn't figure it out.
I was always relying on offset, which is a volatile function and would be bad all right.
But here is the thing.
So we know we're going to do an X look up of Fig into this range and then we get to the what do you want to return?
How do I point to the right spot?
Check this out this formula down here says an excellent cup of Charlie into F2:I2. But what do we want to return?
We want to return the whole rectangular range and just like how XLOOKUP could return all the months January through December, going across this is going to return all of the cells going down, so when I'm looking for Charlie.
It gives me 24, 32, 40, 48, 56, ... right?
And if I would change this to look for Andy?
Then it returns 18, 24 30, 36..., right?
It's it's a beautiful thing returning all those values, but what I found is I'm going to take this whole formula right here that XLOOKUP.
Ctrl C and use that formula as the third argument to another XLOOKUP. So we have our first XLOOKUP. We're looking at B3.
We arere looking at fig in this list here and for the third argument, where do we want to find it?
It's going to be in that range.
This range right here. And when I evaluate formula.
So I'm looking at B3, that's fig.
Then it jumps over to the XLOOKUP. Find Charlie, it says and check this out.
What's going to happen when I click evaluate?
It's not going to give me 24. 32. 40. It's going to give me the reference.
That is beautiful and elegant, and the INDEX and MATCH people will love it because no one else will be able to figure out how it works. That seems to really be a big thing for them.
So awesome solution here.
If you simply hate this solution and you can't give up your INDEX MATCH MATCH, I'm going to ask you at least to switch over to the INDEX XMATCH XMATCH.
The beautiful thing about XMATCH is it defaults to comma zero, defaults to an exact match, so you don't need the third argument.
You don't need the comma zero there or the comma zero there.
XMATCH is a new function that came along yesterday along with XLOOKUP. Alright, so you have your old INDEX/MATCH/MATCH.
The new XLOOKUP/XLOOKUP, I call it the XX. Or the INDEX/XMATCH/XMATCH.
Three different ways to solve that problem.
All this is super exciting.
It went up to 50% of Office 365 Insiders Fast yesterday. I'm hoping that this rolls out.
I mean, it can't be that hard. It's not like dynamic arrays.
They should just roll this out.
Would be nice if it came out at Ignite, which happens in November.
My seminars left outside of Florida: Excelapalooza, Springfield, Chicago, Appleton, Chattanooga. Catch me out.
I also found out I will be presenting it Ignite which is in Orlando, FL.
That will be in early November, check out my book MrExcel, LX the Holy Grail of Excel.
Tips has everything except for these two new functions XLOOKUP and XMATCH and hey yeah, I guess I'm going to brag my video on YouTube was out without minutes after XLOOKUP came out yesterday.
If you want to hear the cool new stuff that hits Excel, click SUBSCRIBE and ring the Bell.
I want to thank you for stopping by.
I will see you next time for another netcast for MrExcel.
