Matching data between two Excel worksheets where you need to match both the city and the tax rate in order to find the match. This video shows three different methods.
Table of Contents
(0:00) Welcome
(1:01) Using XLOOKUP for two-way match
(2:15) Using VLOOKUP with concatenated key
(3:18) Using three queries in Power Query for two-way lookup
Table of Contents
(0:00) Welcome
(1:01) Using XLOOKUP for two-way match
(2:15) Using VLOOKUP with concatenated key
(3:18) Using three queries in Power Query for two-way lookup
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2443.
A two-way look up, three ways.
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
A great question sent in today from Minnesota. They have two worksheets and they'd like to do a VLOOKUP, but it's not just a regular VLOOKUP because the sales tax file can have two rates for the same city.
So down here on the tax worksheet, Abbeville has a 6.5 rate and a 9.0 rate and this is the tax code for the first one and the tax code for the second one.
On the place where they're doing the VLOOKUP, Abbeville.
(I don't know how to pronounce that).
We have a rate of nine, so what's the tax code?
And so in that case it would be 35010.
Now three different ways that I can think of to solve this.
The first and easiest way would be to use an XLOOKUP, but the problem is you may or may not have an XLOOKUP.
Right, so here's our tax worksheet that has city, rate and then what the tax code is.
And then back here we have city and rate.
So =XLOOKUP(.
We are going to look up the concatenation of A2 ampersand B2.
So we're looking up Centerville0.015, comma.
First thing we want to do is where is the lookup array.
So after typing the comma, I'm going to press control page down.
And we take everything in A2 to A144.
That was Ctrl+Shift+Down Arrow.
I'll press the F4 key.
Ampersand and then Ctrl+Shift+Down Arrow from B2.
Again, press the F4 key.
So our lookup table is going to be everything in A, concatenated with everything in B.
And the beautiful thing about XLOOKUP is that it can deal with that.
Comma and then the Results array.
It's going to be over here in column C, so we'll start in C2 and Ctrl+Shift+Down Arrow, press F4 again.
XLOOKUP - we don't have to say that we're looking for an exact match, it's automatic.
So we just have three arguments there and press enter.
Centerville 0.015 is 38219.
Let's check that.
So up here at the top Centerville 0.015 is 38219 - beautiful.
It looks like it's working.
Double Click to copy it down, right?
So that's actually my method 2 - uing XLOOKUP.
And while I was doing this, I realized, well wait, the method three wouldn't be that much worse.
In case you don't have XLOOKUP, you certainly have VLOOKUP - everyone has VLOOKUP.
And so to do this I would come back here.
And I would either insert a new column between B&C or a column to the left.
I don't know which one, I don't know.
Let's just go here and I'll call it Key, the Key field.
So equal Afton B2, ampersand C2.
So equal B2 ampersand C2.
Double-click and copy that down.
OK, so now I have a concatenated key and my VLOOKUP will be a little bit simpler.
So the method here will be =VLOOKUP of Centerville, ampersand, 0.015 comma and then Ctrl+PageDown to get to the next worksheet.
And here it'll be all four columns, we will press F4.
And we want the 4th column and we have to say we want exactly match, so comma false like that.
38219 – double-click and copy that down.
So two different ways.
The third way, in theory, is the easiest, but it's actually the most destructive.
So we start out here with Centerville and rate.
And our data back here.
And for each one we either need to create a named range or convert it to a table (so Ctrl+T for a table).
And we'll call this the state sheet, so state.
And then here control T for table.
And we'll call this the Tax sheet.
Alright, now two steps that seem like they're pointless, but it really is important.
OK, so we are on our State table and we're going to choose Data, From Sheet.
Now if you don't have XLOOKUP, there's a good chance that your Excel says From Table/Range.
It's in the same general area - in the Get and Transform Data.
So we choose From Sheet.
And the only thing I have to do here, I don't need the tax code.
I'll remove that.
And I need to add a index column.
If you're a programmer, you are going to choose From Zero.
But if you're an accountant, you're going to choose From One.
Everyone else - I don't know… whatever.
Whatever you want to do, it doesn't matter either way.
All right, that's good.
Home, Don't click Close and Load - click Close and Load to….
And say Only Create a Connection.
Alright, that's first extra step.
And then on the Tax sheet this will actually be easier.
From Sheet or From Table/Range and then just directly to Home, Close and Load To….
I want to create a connection.
Then we'll come back here to our State sheet.
Just kind of off here to the right a little bit.
Get Data, Combine queries, Merge.
And we are going to merge those two connections we just created.
So we'll start from the State sheet.
And link to the Tax sheet.
Now on the state sheet, we're going to click on City first and then Ctrl+click on Rate and you'll see the little “1” and “2” to show you what order you clicked on those.
City and then Rate - one and the two.
Everything from the first, matching from the second.
Click OK.
Alright, and this step right here we're still fine - Centerville, Grant, and Saint Paul.
The thing that really annoys me here is when I get the tax rate out.
Right, so I expand this, choose tax code.
I don't need the original column name as a prefix.
Centerville, Grant, St. Paul.
Click OK.
I have no idea why it's reordered in this order.
It's not alphabetical.
It makes no sense to me at all.
I'm starting from the left table.
It should stay in the same order as the left table.
That's why we had the index column back here.
Just super insane that we had to do that.
At this point, I'm really tempted to delete the index column, but I'm just going to leave it there - out of superstition.
I guess I should test it both ways.
Centerville Grant St. Paul.
Let's try it without.
We will remove.
Centerville, Grant, Saint Paul.
Close and Load, Close and Load to.
This time we're going to bring it to the grid, but to an existing worksheet right there in G1.
Click OK.
Alright, and it looks like all of this stuff stayed in the same sequence.
If you don't have that index column, you end up with data back here in the wrong sequence.
And then there's no easy way to just take this, Ctrl+C and paste special values into the tax code.
Now, if you really want to be careful (and I actually did this the first time).
Check and make sure that Centerville is equal to Centerville and that they're all identical all the way down and they are.
We can test that with a little =AND at the bottom, and that will be true.
Alright, there you go.
I was really thrown for a loop loop the first time I recorded this.
When the data came back and it wasn't in the same sequence.
I was like “what the… why why?” And I've heard, I've heard you can't trust power query to sort your data.
I've heard that.
But I’ve never been burned by it before.
Alright, so there you go three different ways.
The XLOOKUP.
I think if you have XLOOKUP is probably the way to go.
If you don't have XLOOKUP, then the concatenated key with a VLOOKUP.
Or if you need something that you can refresh then the power query - the three queries in power query.
You know, the thing I don't know here is I don't know what else is in the data.
I have to believe that there were more columns here, and they sent me a simplified version.
So it's probably more complicated than than what they sent - It always is.
Alright, OK, this book, MrExcel 2021 Unmasking Excel - definitely a lot of VLOOKUP.
Twelve XLOOKUP examples in there.
And Power Query as well.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hit it, Nancy.
A two-way look up, three ways.
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
A great question sent in today from Minnesota. They have two worksheets and they'd like to do a VLOOKUP, but it's not just a regular VLOOKUP because the sales tax file can have two rates for the same city.
So down here on the tax worksheet, Abbeville has a 6.5 rate and a 9.0 rate and this is the tax code for the first one and the tax code for the second one.
On the place where they're doing the VLOOKUP, Abbeville.
(I don't know how to pronounce that).
We have a rate of nine, so what's the tax code?
And so in that case it would be 35010.
Now three different ways that I can think of to solve this.
The first and easiest way would be to use an XLOOKUP, but the problem is you may or may not have an XLOOKUP.
Right, so here's our tax worksheet that has city, rate and then what the tax code is.
And then back here we have city and rate.
So =XLOOKUP(.
We are going to look up the concatenation of A2 ampersand B2.
So we're looking up Centerville0.015, comma.
First thing we want to do is where is the lookup array.
So after typing the comma, I'm going to press control page down.
And we take everything in A2 to A144.
That was Ctrl+Shift+Down Arrow.
I'll press the F4 key.
Ampersand and then Ctrl+Shift+Down Arrow from B2.
Again, press the F4 key.
So our lookup table is going to be everything in A, concatenated with everything in B.
And the beautiful thing about XLOOKUP is that it can deal with that.
Comma and then the Results array.
It's going to be over here in column C, so we'll start in C2 and Ctrl+Shift+Down Arrow, press F4 again.
XLOOKUP - we don't have to say that we're looking for an exact match, it's automatic.
So we just have three arguments there and press enter.
Centerville 0.015 is 38219.
Let's check that.
So up here at the top Centerville 0.015 is 38219 - beautiful.
It looks like it's working.
Double Click to copy it down, right?
So that's actually my method 2 - uing XLOOKUP.
And while I was doing this, I realized, well wait, the method three wouldn't be that much worse.
In case you don't have XLOOKUP, you certainly have VLOOKUP - everyone has VLOOKUP.
And so to do this I would come back here.
And I would either insert a new column between B&C or a column to the left.
I don't know which one, I don't know.
Let's just go here and I'll call it Key, the Key field.
So equal Afton B2, ampersand C2.
So equal B2 ampersand C2.
Double-click and copy that down.
OK, so now I have a concatenated key and my VLOOKUP will be a little bit simpler.
So the method here will be =VLOOKUP of Centerville, ampersand, 0.015 comma and then Ctrl+PageDown to get to the next worksheet.
And here it'll be all four columns, we will press F4.
And we want the 4th column and we have to say we want exactly match, so comma false like that.
38219 – double-click and copy that down.
So two different ways.
The third way, in theory, is the easiest, but it's actually the most destructive.
So we start out here with Centerville and rate.
And our data back here.
And for each one we either need to create a named range or convert it to a table (so Ctrl+T for a table).
And we'll call this the state sheet, so state.
And then here control T for table.
And we'll call this the Tax sheet.
Alright, now two steps that seem like they're pointless, but it really is important.
OK, so we are on our State table and we're going to choose Data, From Sheet.
Now if you don't have XLOOKUP, there's a good chance that your Excel says From Table/Range.
It's in the same general area - in the Get and Transform Data.
So we choose From Sheet.
And the only thing I have to do here, I don't need the tax code.
I'll remove that.
And I need to add a index column.
If you're a programmer, you are going to choose From Zero.
But if you're an accountant, you're going to choose From One.
Everyone else - I don't know… whatever.
Whatever you want to do, it doesn't matter either way.
All right, that's good.
Home, Don't click Close and Load - click Close and Load to….
And say Only Create a Connection.
Alright, that's first extra step.
And then on the Tax sheet this will actually be easier.
From Sheet or From Table/Range and then just directly to Home, Close and Load To….
I want to create a connection.
Then we'll come back here to our State sheet.
Just kind of off here to the right a little bit.
Get Data, Combine queries, Merge.
And we are going to merge those two connections we just created.
So we'll start from the State sheet.
And link to the Tax sheet.
Now on the state sheet, we're going to click on City first and then Ctrl+click on Rate and you'll see the little “1” and “2” to show you what order you clicked on those.
City and then Rate - one and the two.
Everything from the first, matching from the second.
Click OK.
Alright, and this step right here we're still fine - Centerville, Grant, and Saint Paul.
The thing that really annoys me here is when I get the tax rate out.
Right, so I expand this, choose tax code.
I don't need the original column name as a prefix.
Centerville, Grant, St. Paul.
Click OK.
I have no idea why it's reordered in this order.
It's not alphabetical.
It makes no sense to me at all.
I'm starting from the left table.
It should stay in the same order as the left table.
That's why we had the index column back here.
Just super insane that we had to do that.
At this point, I'm really tempted to delete the index column, but I'm just going to leave it there - out of superstition.
I guess I should test it both ways.
Centerville Grant St. Paul.
Let's try it without.
We will remove.
Centerville, Grant, Saint Paul.
Close and Load, Close and Load to.
This time we're going to bring it to the grid, but to an existing worksheet right there in G1.
Click OK.
Alright, and it looks like all of this stuff stayed in the same sequence.
If you don't have that index column, you end up with data back here in the wrong sequence.
And then there's no easy way to just take this, Ctrl+C and paste special values into the tax code.
Now, if you really want to be careful (and I actually did this the first time).
Check and make sure that Centerville is equal to Centerville and that they're all identical all the way down and they are.
We can test that with a little =AND at the bottom, and that will be true.
Alright, there you go.
I was really thrown for a loop loop the first time I recorded this.
When the data came back and it wasn't in the same sequence.
I was like “what the… why why?” And I've heard, I've heard you can't trust power query to sort your data.
I've heard that.
But I’ve never been burned by it before.
Alright, so there you go three different ways.
The XLOOKUP.
I think if you have XLOOKUP is probably the way to go.
If you don't have XLOOKUP, then the concatenated key with a VLOOKUP.
Or if you need something that you can refresh then the power query - the three queries in power query.
You know, the thing I don't know here is I don't know what else is in the data.
I have to believe that there were more columns here, and they sent me a simplified version.
So it's probably more complicated than than what they sent - It always is.
Alright, OK, this book, MrExcel 2021 Unmasking Excel - definitely a lot of VLOOKUP.
Twelve XLOOKUP examples in there.
And Power Query as well.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hit it, Nancy.
