Today's question comes from Ron and he would like to know, "when would I use OFFSET in conjunction with the VLOOKUP Function?" In Episode #1619, Bill "MrExcel" Jelen explains the use of OFFSET and VLOOKUP together as well as provides us a great example.
...This blog is 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 blog is 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, episode 1619.
OFFSET for a VLOOKUP Table.
Today's question sent in by Ron.
Ron asked when you would ever use offset in conjunction with vlookup?
And one example I have, I've seen this before you know, we have a count, our cost centers in it, in counting and sometimes there's multiple companies.
So, there's a company 100, company 200 company 300, and based on whichever company has entered here.
We want to go use a different vlookup table.
Alright! So, the offset function is very, very flexible.
It says you know, hey!
We're going to start from a particular cell, in this case it's gonna start from B1 and then how many rows down from there.
Do we go to define the start of the lookup table?
So, if this is 100, we're gonna go down one row.
But if it says 200, then we're gonna go down eight rows.
So, we're gonna fill in that number of rows down for the number of columns to the right.
Well, that's just gonna be hard-coded to 0.
We're not going to move any columns to the right to get to the top left corner cell, off the range, but then the height and width of the range.
Well, the width in this case I know, It's just two, but the height is going to be different every time.
All right! So, we need to be able to fill in those two question marks there, where does this company start equal MATCH.
Match of the 200 comma and we'll start here in A2 and go down and we want an exact match.
Alright! So, that tells us that the 200, see it doesn't start in row 8, it starts in the 8th row of that range.
So, when we start here in B1 and we go down 8 cells.
We'll get to the top left-hand corner cell.
All right! How many rows for this company?
Equal count if.
Count, how many times in column A we have 200.
I'll say that for company 200, it is 6 rows.
If we ask for company 100, 7 rows, all right!
So, it's gonna be a different number of rows every single time.
So, kind of just practicing here, equal offset from B1, press [ F4 ], comma the number of rows down, is going to be this answer, comma the number of columns is zero, the height is this answer and the width is 2, all right!
So, we have that whole little formula fragment there.
I'm gonna copy that [ ctrl C ], to copy, press [ escape ] and up here in vlookup.
I'm gonna paste that formula fragment, right there for the lookup table.
For the lookup table then at this point I can get rid of that.
'apostrophe (') and so, company 200, account center 1015, 2048.
I'll try a company 100 and account center 1030, it's get the 4.
All right! So, it's actually working.
At this point, now we can make one super mega formula.
So, here in F7, we have those characters, I'll copy those from the formula bar, and then I'll do the same thing for F10 to end up with a really, really large unwieldy formula.
I always build these in pieces, at first just that way I can break it down into the component parts and then you can put it all back together.
If you would like at the end here, let's get that so we can see the whole formula here in our expanding formula bar you know, that's a confusing thing.
But hopefully with this explanation here, we're finding of where the company starts.
How many rows for the company putting that into the offset and then using that offset as the table range in our vlookup.
I thank Ron for sending that question in.
I wanna thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1619.
OFFSET for a VLOOKUP Table.
Today's question sent in by Ron.
Ron asked when you would ever use offset in conjunction with vlookup?
And one example I have, I've seen this before you know, we have a count, our cost centers in it, in counting and sometimes there's multiple companies.
So, there's a company 100, company 200 company 300, and based on whichever company has entered here.
We want to go use a different vlookup table.
Alright! So, the offset function is very, very flexible.
It says you know, hey!
We're going to start from a particular cell, in this case it's gonna start from B1 and then how many rows down from there.
Do we go to define the start of the lookup table?
So, if this is 100, we're gonna go down one row.
But if it says 200, then we're gonna go down eight rows.
So, we're gonna fill in that number of rows down for the number of columns to the right.
Well, that's just gonna be hard-coded to 0.
We're not going to move any columns to the right to get to the top left corner cell, off the range, but then the height and width of the range.
Well, the width in this case I know, It's just two, but the height is going to be different every time.
All right! So, we need to be able to fill in those two question marks there, where does this company start equal MATCH.
Match of the 200 comma and we'll start here in A2 and go down and we want an exact match.
Alright! So, that tells us that the 200, see it doesn't start in row 8, it starts in the 8th row of that range.
So, when we start here in B1 and we go down 8 cells.
We'll get to the top left-hand corner cell.
All right! How many rows for this company?
Equal count if.
Count, how many times in column A we have 200.
I'll say that for company 200, it is 6 rows.
If we ask for company 100, 7 rows, all right!
So, it's gonna be a different number of rows every single time.
So, kind of just practicing here, equal offset from B1, press [ F4 ], comma the number of rows down, is going to be this answer, comma the number of columns is zero, the height is this answer and the width is 2, all right!
So, we have that whole little formula fragment there.
I'm gonna copy that [ ctrl C ], to copy, press [ escape ] and up here in vlookup.
I'm gonna paste that formula fragment, right there for the lookup table.
For the lookup table then at this point I can get rid of that.
'apostrophe (') and so, company 200, account center 1015, 2048.
I'll try a company 100 and account center 1030, it's get the 4.
All right! So, it's actually working.
At this point, now we can make one super mega formula.
So, here in F7, we have those characters, I'll copy those from the formula bar, and then I'll do the same thing for F10 to end up with a really, really large unwieldy formula.
I always build these in pieces, at first just that way I can break it down into the component parts and then you can put it all back together.
If you would like at the end here, let's get that so we can see the whole formula here in our expanding formula bar you know, that's a confusing thing.
But hopefully with this explanation here, we're finding of where the company starts.
How many rows for the company putting that into the offset and then using that offset as the table range in our vlookup.
I thank Ron for sending that question in.
I wanna thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
