Todays question involves doing a Range Lookup using a VLOOKUP Formula. Bill shows us how to set this up based on the Data Range provided and explains why today's VLOOKUP will be ending with a 'TRUE' statement instead of the usual 'False'. See how and why its done in Episode #1570.
...This a the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This a the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast: This is VLOOKUP Range.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen from MrExcel.
Today's question sent by Jer, Jer wants to do a VLOOKUP and he wants to know if we can return range, all right, so he has some cost centers here and he says anything from zero zero zero one two zero nine and nine should be called fixed assets.
Oh yeah, there is an obscure version of VLOOKUP that will do this.
Let me turn this into a nice little table here.
I'm gonna use data text to columns.
I'll DE fixed width and let's see, that looks pretty good right there.
We'll keep that, I don't need this one.
I actually don't need that one but we'll leave it just to show how it works and click finish.
So there's my table and again really all we need is the starting number and the description.
We don't need the ending number.
So, alright, so we start out with equal VLOOKUP.
We're looking up the 4750 comma.
Here's our table range.
I'll press F4 to lock that down and we want the one two third column comma, 3comma.
What do we put at the end of every single view look up, false.
But not this view look at what we're doing a range look up, the approximate match it's looking for something in a range of numbers.
We can either put true or just leave it off and Excel will do the kind of look up that Jer is looking for now.
These over here ran between so it's changing all the time.
So 1000 to 1999 should be current assets, 23 current liabilities.
Oh one five three fixed assets right there.
How do I get the leading zeros to show that is in format cells' Custom I used 0 0 0 0, now that's funny that they think that that is a postal code for Portugal.
Ok, whatever, it's four zeros to get those leading zeros to show.
So these are just numbers over here they're not text and we should be good to go.
You can actually, let's delete that extra column, just to prove that we don't need it, change this back to column two, so it doesn't need the the ending range, is just anything from one.
If it's one or higher here and get fixed assets until you get to 1000.
Then 1000 or higher you're going to get current assets.
These of course have to be sorted a saying this is the only time you have to sort.
I have to thank Jerry Simon apportion.
Well, thank you for stopping by.
See you next time, another netcast from MrExcel.
Learn Excel from MrExcel podcast: This is VLOOKUP Range.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen from MrExcel.
Today's question sent by Jer, Jer wants to do a VLOOKUP and he wants to know if we can return range, all right, so he has some cost centers here and he says anything from zero zero zero one two zero nine and nine should be called fixed assets.
Oh yeah, there is an obscure version of VLOOKUP that will do this.
Let me turn this into a nice little table here.
I'm gonna use data text to columns.
I'll DE fixed width and let's see, that looks pretty good right there.
We'll keep that, I don't need this one.
I actually don't need that one but we'll leave it just to show how it works and click finish.
So there's my table and again really all we need is the starting number and the description.
We don't need the ending number.
So, alright, so we start out with equal VLOOKUP.
We're looking up the 4750 comma.
Here's our table range.
I'll press F4 to lock that down and we want the one two third column comma, 3comma.
What do we put at the end of every single view look up, false.
But not this view look at what we're doing a range look up, the approximate match it's looking for something in a range of numbers.
We can either put true or just leave it off and Excel will do the kind of look up that Jer is looking for now.
These over here ran between so it's changing all the time.
So 1000 to 1999 should be current assets, 23 current liabilities.
Oh one five three fixed assets right there.
How do I get the leading zeros to show that is in format cells' Custom I used 0 0 0 0, now that's funny that they think that that is a postal code for Portugal.
Ok, whatever, it's four zeros to get those leading zeros to show.
So these are just numbers over here they're not text and we should be good to go.
You can actually, let's delete that extra column, just to prove that we don't need it, change this back to column two, so it doesn't need the the ending range, is just anything from one.
If it's one or higher here and get fixed assets until you get to 1000.
Then 1000 or higher you're going to get current assets.
These of course have to be sorted a saying this is the only time you have to sort.
I have to thank Jerry Simon apportion.
Well, thank you for stopping by.
See you next time, another netcast from MrExcel.
