Solving the same problem from episode 1203, doing a VLOOKUP that matches two columns, but without using a concatenated key. This solution uses the OFFSET function to dynamically change the position of the lookup table.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Let's follow up on yesterday's netcast, where we were trying to do a lookup that required two columns.
Yesterday, I used to concatenate a key, but if you're not allowed to add something to this data, you know it's in an external workbook or something like that.
Then we have to do something, we're going to geek out here basically.
I’m going to use the MATCH function, to figure out where this particular company, the company in E2, starts.
So I want to figure out, you know, how many rows down, basically, that goes.
We'll put 0, which is like doing a FALSE in VLOOKUP, it says that company 300 starts in the 9th row of that range.
If I put in company 200, you see that it would start in the 5th row of this range.
And then how many rows in the company?
So =COUNTIF, how many items over there in column are equal to this particular company?
And that's going to tell us that we have 4.
So now that I know position, where it starts and how large it is, we can use a function called =OFFSET.
So OFFSET is going to start up here in B1, I’ll press the F4 key, and then it wants to know how many rows down.
Well, how many rows down is going to be the answer to the first formula, how many columns over?
I’m not going to go any columns over to start the reference, and then once know how tall it's going to be, that’s the answer to our second formula.
And how wide is going to be, well that’s two columns in this case, because I need to get that amount.
So OFFSET is a great way to positionally refer to the lookup table.
Alright, so now that I have that OFFSET, I want to go and actually build my VLOOKUP.
So I’m going to look up that value in F2, the amount, or the accountant, I'm sorry, in the dynamic VLOOKUP range, and ,2,FALSE just like normal.
And the advantage here is, you get one formula without having to add a concatenated key.
Alright, now that I built that formula in multiple pieces, I’m going to copy those characters and paste them in here, and then copy the characters from the second question, everything but the equal sign.
Of course, Ctrl+C, and paste them in right here, Ctrl+V, so we end up with one big, huge formula that I can actually now cut and paste up here.
And it looks like we have something very impressive, of course, when someone looks at that formula, but in fact built in several different steps.
So there you have it, a great way to do a lookup that requires two columns when you can't add a concatenated key.
Hey, I want to thank you for stopping by, tomorrow I promise something much easier, see you next time for another netcast from MrExcel!
Hey!
Boy, I almost forgot our sponsor, “Easy-XL”!
I just checked it, makes it really easy to do this 2-way lookup, the 2-column lookup.
So what I had to do was, I had to move things to two different sheets.
Here's the values, I want to look up on Sheet1, has Company and Account, and then here's the lookup table in Sheet2, Company, Account, and Amount.
I go back to Sheet1, Merge Sheets, merge this with the Lookup sheet, select Based On both the Company and Account.
Of course, you have more fields, it’s be easy to choose those, and I want to join it in the Amount, Rows in Sheet1 that match a row in Sheet2, perfect.
Click Finish, and we get a brand new sheet with a Company, the Account, and the lookup value from Sheet3, much easier than any of the methods in the last couple of days.
There you have it, easy-xl.com, free 30-day trial, give it a shot!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Let's follow up on yesterday's netcast, where we were trying to do a lookup that required two columns.
Yesterday, I used to concatenate a key, but if you're not allowed to add something to this data, you know it's in an external workbook or something like that.
Then we have to do something, we're going to geek out here basically.
I’m going to use the MATCH function, to figure out where this particular company, the company in E2, starts.
So I want to figure out, you know, how many rows down, basically, that goes.
We'll put 0, which is like doing a FALSE in VLOOKUP, it says that company 300 starts in the 9th row of that range.
If I put in company 200, you see that it would start in the 5th row of this range.
And then how many rows in the company?
So =COUNTIF, how many items over there in column are equal to this particular company?
And that's going to tell us that we have 4.
So now that I know position, where it starts and how large it is, we can use a function called =OFFSET.
So OFFSET is going to start up here in B1, I’ll press the F4 key, and then it wants to know how many rows down.
Well, how many rows down is going to be the answer to the first formula, how many columns over?
I’m not going to go any columns over to start the reference, and then once know how tall it's going to be, that’s the answer to our second formula.
And how wide is going to be, well that’s two columns in this case, because I need to get that amount.
So OFFSET is a great way to positionally refer to the lookup table.
Alright, so now that I have that OFFSET, I want to go and actually build my VLOOKUP.
So I’m going to look up that value in F2, the amount, or the accountant, I'm sorry, in the dynamic VLOOKUP range, and ,2,FALSE just like normal.
And the advantage here is, you get one formula without having to add a concatenated key.
Alright, now that I built that formula in multiple pieces, I’m going to copy those characters and paste them in here, and then copy the characters from the second question, everything but the equal sign.
Of course, Ctrl+C, and paste them in right here, Ctrl+V, so we end up with one big, huge formula that I can actually now cut and paste up here.
And it looks like we have something very impressive, of course, when someone looks at that formula, but in fact built in several different steps.
So there you have it, a great way to do a lookup that requires two columns when you can't add a concatenated key.
Hey, I want to thank you for stopping by, tomorrow I promise something much easier, see you next time for another netcast from MrExcel!
Hey!
Boy, I almost forgot our sponsor, “Easy-XL”!
I just checked it, makes it really easy to do this 2-way lookup, the 2-column lookup.
So what I had to do was, I had to move things to two different sheets.
Here's the values, I want to look up on Sheet1, has Company and Account, and then here's the lookup table in Sheet2, Company, Account, and Amount.
I go back to Sheet1, Merge Sheets, merge this with the Lookup sheet, select Based On both the Company and Account.
Of course, you have more fields, it’s be easy to choose those, and I want to join it in the Amount, Rows in Sheet1 that match a row in Sheet2, perfect.
Click Finish, and we get a brand new sheet with a Company, the Account, and the lookup value from Sheet3, much easier than any of the methods in the last couple of days.
There you have it, easy-xl.com, free 30-day trial, give it a shot!
