How can you do a lookup to find records that match 2 columns? Episode 1203 shows how to use a concatenated key to solve the problem.
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.
Well, today this question was asked in two of the seminars I did in last week, so I was down in Missouri and Illinois, came up in both of those seminars.
Someone had to do a not a 2-way look up, but they had to look up two different columns, so they had a series of like GL entries with both a company, and an account, and an amount.
So, for a given company and account, how do you find the amount?
And well, there's two ways to do this, today I'm going to do I think the easier way, and tomorrow we'll take a look at the harder way.
So what I'm going to do is, I'm going to insert a brand new column here, I'm going to call that Key.
And the Key is going to concatenate both the company and the account, and you can use whatever method you'd like.
Now I'm going to use an actual concatenation, so the &, and then in quotes I'll put something to break it apart, and then & the account, will double-click to shoot that down.
A little bit wider, so you can see what's happening now.
Someone in the room said “Well hey, If this is actually the way the data looks, you should be able to just add the company and the account, and it will be unique.
Whatever you have to do, you have to make sure that it's unique.” Once you have that concatenated key, then =VLOOKUP of the company, we use exact same formula here, &”-“& and the account.
So I'm looking up that concatenated key over in this table here, choose that range, press F4, ,2,FALSE , just like the usual VLOOKUP, and you see that that works, so 212, the amount is 1212, that works.
If we change the company 300, we get the same account, 1723, so it's all good.
So if you're allowed to change the data to add this concatenated key here, I think that's the easy way to go.
Now tomorrow I'll take a look at a different way.
Frankly, once you see that formula from tomorrow, you'll be happy to use this method here.
Hey, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, today this question was asked in two of the seminars I did in last week, so I was down in Missouri and Illinois, came up in both of those seminars.
Someone had to do a not a 2-way look up, but they had to look up two different columns, so they had a series of like GL entries with both a company, and an account, and an amount.
So, for a given company and account, how do you find the amount?
And well, there's two ways to do this, today I'm going to do I think the easier way, and tomorrow we'll take a look at the harder way.
So what I'm going to do is, I'm going to insert a brand new column here, I'm going to call that Key.
And the Key is going to concatenate both the company and the account, and you can use whatever method you'd like.
Now I'm going to use an actual concatenation, so the &, and then in quotes I'll put something to break it apart, and then & the account, will double-click to shoot that down.
A little bit wider, so you can see what's happening now.
Someone in the room said “Well hey, If this is actually the way the data looks, you should be able to just add the company and the account, and it will be unique.
Whatever you have to do, you have to make sure that it's unique.” Once you have that concatenated key, then =VLOOKUP of the company, we use exact same formula here, &”-“& and the account.
So I'm looking up that concatenated key over in this table here, choose that range, press F4, ,2,FALSE , just like the usual VLOOKUP, and you see that that works, so 212, the amount is 1212, that works.
If we change the company 300, we get the same account, 1723, so it's all good.
So if you're allowed to change the data to add this concatenated key here, I think that's the easy way to go.
Now tomorrow I'll take a look at a different way.
Frankly, once you see that formula from tomorrow, you'll be happy to use this method here.
Hey, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
