Historically, using VLOOKUP to look for a value to the left is VLOOKUP's Kryptonite. While VLOOKUP is awesome, there is not an easy way to look to the left. While this episode briefly gives a nod to trickster methods like CHOOSE and INDEX/MATCH, the topic today is how easy it is to XLOOKUP to the left.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2302.
Can You VLOOKUP to the left?
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
This classic Letterpress poster from Just a Jar Design Press: "A value to the left is VLOOKUP's kryptonite".
That's the question for today. Is it possible to have VLOOKUP go to the left?
Like could you put in negative one there?
No you can't you can't.
It's a value error now there have been work arounds in the past but this podcast is not about those work arounds.
First work around.
Hey it's just copy and paste. Control C.
Come over here Ctrl V and now we're doing a VLOOKUP to the right. But that's cheating.
The Index and Match folks (in fact, that poster was brought to you by index and match...) Index and Match can look to the left, but we're not here to talk about Index and Match today.
Or the Excel tricksters that use the CHOOSE function to rearrange these two columns inside and pass that to VLOOKUP.
We're not here to talk about that.
Instead, we're here to talk about the great new function that's just coming out to Excel. I called XLOOKUP. So here's how XLOOKUP works.
We are looking at W25-6.
That seems exactly like VLOOKUP starts, but the difference is were are now passing it a Lookup Array and a Return Array. So Lookup Array.
What are we looking at? Were looking at the item numbers here.
And I'll press F4 to put those dollar signs in.
And then what do we want to return?
We want to return these values here in Column G, which is the left of the data and XLOOKUP doesn't care. We don't have to put common false or comma zero at the end.
No, because XLOOKUP automatically defaults to an exact match is just a beautiful beautiful thing.
So the fastest, easiest way to VLOOKUP to the left is the brand new XLOOKUP function.
This is the point where normally I'd ask you to Buy the book, but we're coming up on Small Business Saturday. That is November 30th, 2019.
Check down in the YouTube description.
I got a great deal for you.
Buy this book and I'll get you an original copy of the Bobby Rosenstock poster for free. If you like we see here please subscribe and ring that bell.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Can You VLOOKUP to the left?
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
This classic Letterpress poster from Just a Jar Design Press: "A value to the left is VLOOKUP's kryptonite".
That's the question for today. Is it possible to have VLOOKUP go to the left?
Like could you put in negative one there?
No you can't you can't.
It's a value error now there have been work arounds in the past but this podcast is not about those work arounds.
First work around.
Hey it's just copy and paste. Control C.
Come over here Ctrl V and now we're doing a VLOOKUP to the right. But that's cheating.
The Index and Match folks (in fact, that poster was brought to you by index and match...) Index and Match can look to the left, but we're not here to talk about Index and Match today.
Or the Excel tricksters that use the CHOOSE function to rearrange these two columns inside and pass that to VLOOKUP.
We're not here to talk about that.
Instead, we're here to talk about the great new function that's just coming out to Excel. I called XLOOKUP. So here's how XLOOKUP works.
We are looking at W25-6.
That seems exactly like VLOOKUP starts, but the difference is were are now passing it a Lookup Array and a Return Array. So Lookup Array.
What are we looking at? Were looking at the item numbers here.
And I'll press F4 to put those dollar signs in.
And then what do we want to return?
We want to return these values here in Column G, which is the left of the data and XLOOKUP doesn't care. We don't have to put common false or comma zero at the end.
No, because XLOOKUP automatically defaults to an exact match is just a beautiful beautiful thing.
So the fastest, easiest way to VLOOKUP to the left is the brand new XLOOKUP function.
This is the point where normally I'd ask you to Buy the book, but we're coming up on Small Business Saturday. That is November 30th, 2019.
Check down in the YouTube description.
I got a great deal for you.
Buy this book and I'll get you an original copy of the Bobby Rosenstock poster for free. If you like we see here please subscribe and ring that bell.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
