You need to do a VLOOKUP that will look up two values from a table. In Episode 889, I will show a method using a concatenated key field to enable VLOOKUP to work.
This video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This episode goes all the way back to Episode 383, where I showed how to set up dependent validation.
So someone shows in Ohio and they have a list of Ohio counties.
But if they would come up here and choose Texas, then they get a list of Texas counties instead of the Ohio counties.
So, kind of a cool trick, and someone had used that trick and they said, "Okay, well that's cool, but now I need to do a Lookup-- I need to go lookup both the State and the county." And I'm going to do this two different ways.
I'm going to do one method today that requires you to change your data a little bit.
This method is involving something called a concatenated key.
So you see here, I have State and County, and then the value we want to look up between that.
I'm going to insert a new column, I'm going to call it the Key column, and basically we're going to put a formula in here that takes whatever is in A2, and ampersand, and whatever is in B2, and that's going to join together the State and the County to make it unique.
Because as you think about all the counties across the United States-- like, I'm very near Summit County in Ohio, I know there's a Summit County in Utah and Colorado, and probably other places-- and so we need to make sure that that value is unique.
And then here I'm going to do a VLOOKUP-- =VLOOKUP-- but I'm not going to go lookup one particular value; I'm going to go look up the concatenation of the chosen State and County.
So I'm going to go look up that value.
My table is going to use Column C as the first column of the table; press F4; I want the second column and then false; and so there's the answer for that particular County.
And now, if we would change-- let me choose a different County here in Texas, you see that it automatically does that-- now, to change the State, we're going to temporarily get something that doesn't work, but then when I choose a valid County from Ohio it goes and grabs the correct value.
So Method 1, today, we're using a concatenated key: That's where we take the value from A and the value from B and join it together, and then have to look up the concatenation of the State and County.
Tomorrow, we'll take a look at how to solve this using the OFFSET function, so stop back.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
This episode goes all the way back to Episode 383, where I showed how to set up dependent validation.
So someone shows in Ohio and they have a list of Ohio counties.
But if they would come up here and choose Texas, then they get a list of Texas counties instead of the Ohio counties.
So, kind of a cool trick, and someone had used that trick and they said, "Okay, well that's cool, but now I need to do a Lookup-- I need to go lookup both the State and the county." And I'm going to do this two different ways.
I'm going to do one method today that requires you to change your data a little bit.
This method is involving something called a concatenated key.
So you see here, I have State and County, and then the value we want to look up between that.
I'm going to insert a new column, I'm going to call it the Key column, and basically we're going to put a formula in here that takes whatever is in A2, and ampersand, and whatever is in B2, and that's going to join together the State and the County to make it unique.
Because as you think about all the counties across the United States-- like, I'm very near Summit County in Ohio, I know there's a Summit County in Utah and Colorado, and probably other places-- and so we need to make sure that that value is unique.
And then here I'm going to do a VLOOKUP-- =VLOOKUP-- but I'm not going to go lookup one particular value; I'm going to go look up the concatenation of the chosen State and County.
So I'm going to go look up that value.
My table is going to use Column C as the first column of the table; press F4; I want the second column and then false; and so there's the answer for that particular County.
And now, if we would change-- let me choose a different County here in Texas, you see that it automatically does that-- now, to change the State, we're going to temporarily get something that doesn't work, but then when I choose a valid County from Ohio it goes and grabs the correct value.
So Method 1, today, we're using a concatenated key: That's where we take the value from A and the value from B and join it together, and then have to look up the concatenation of the State and County.
Tomorrow, we'll take a look at how to solve this using the OFFSET function, so stop back.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
