A VLOOKUP to convert State Codes to State Names all in an Array Formula - and no backward compatibility! Today, in Episode #1418, Bill shows us how to get this issue fixed along with a brief, but informative look at a few ways to work with VLOOKUP.
Transcript of the video:
MrExcel podcast sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1418.
Unwinding Array.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Boy! This is a winner, today.
Someone sent in a worksheet created by a co-worker and check this out.
They did a vlookup to convert state codes to state names and they put the whole array right there in the formula. Okay!
So, that's cool.
I get it, you know it's better than a whole bunch of IF statements.
I don't know, why they didn't embed the table somewhere else.
But now, this person needs to go the other way, they need to convert from Ohio back to OH.
How can they take this formula and go the other way, well here's what I wanna do, I'm gonna go to the formula.
I'm gonna select all of the characters from that first curly bracket to the last curly bracket, okay!
[ Ctrl C ], to copy those characters to the clipboard then I'm gonna come back to Excel, go back to my sheet, where that other data is and I'm going to select two columns by 50 rows.
Actually, let's just put 51 in there.
I don't know if they have, Washington DC or not.
We'll type in equal sign and then [ ctrl V ] to paste, that whole array in text back and then [ control shift enter ].
BAM!
There is the whole array out of that formula, isn't that wild, okay! Now, this is still an array formula.
So, let's see did we get okay, that Washington's not there.
I'm gonna copy everything that I need except for Washington or the NAs, where Washington should have been.
[ Ctrl C ] and go out here to the right and then [ alt ESV ], paste special values, [ alt E S V ].
To build that now, let's go the other way.
So, we'll copy or cut the state codes, put them to the right of the state names, all right!
So, that's working really, really well.
Let's put Washington DC in there because I think it should be.
There's Washington.
Washington, see period of course, we're hoping if they're going to put it the right way and the code that is DC.
All right! So we have description and state, good enough.
All right! So, now to build our formula equal vlookup, A2 and the array that we want to use it over here press [ F4 ] comma 2 comma false.
Now, we have something working the other way but hey!
What if we don't want that Vlookup and our that Vlookup table embedded out there.
Well, we can turn this address back into an array very, very quickly.
Select those characters and press [ F9 ] and then just [ Enter ].
Copy that down we can actually now delete this array out here or this table out here and our formula continues to work.
All right!
So, very annoying thing that co-worker did there but using a couple of tricks are able to spin that table back, the other way.
Hey! I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1418.
Unwinding Array.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Boy! This is a winner, today.
Someone sent in a worksheet created by a co-worker and check this out.
They did a vlookup to convert state codes to state names and they put the whole array right there in the formula. Okay!
So, that's cool.
I get it, you know it's better than a whole bunch of IF statements.
I don't know, why they didn't embed the table somewhere else.
But now, this person needs to go the other way, they need to convert from Ohio back to OH.
How can they take this formula and go the other way, well here's what I wanna do, I'm gonna go to the formula.
I'm gonna select all of the characters from that first curly bracket to the last curly bracket, okay!
[ Ctrl C ], to copy those characters to the clipboard then I'm gonna come back to Excel, go back to my sheet, where that other data is and I'm going to select two columns by 50 rows.
Actually, let's just put 51 in there.
I don't know if they have, Washington DC or not.
We'll type in equal sign and then [ ctrl V ] to paste, that whole array in text back and then [ control shift enter ].
BAM!
There is the whole array out of that formula, isn't that wild, okay! Now, this is still an array formula.
So, let's see did we get okay, that Washington's not there.
I'm gonna copy everything that I need except for Washington or the NAs, where Washington should have been.
[ Ctrl C ] and go out here to the right and then [ alt ESV ], paste special values, [ alt E S V ].
To build that now, let's go the other way.
So, we'll copy or cut the state codes, put them to the right of the state names, all right!
So, that's working really, really well.
Let's put Washington DC in there because I think it should be.
There's Washington.
Washington, see period of course, we're hoping if they're going to put it the right way and the code that is DC.
All right! So we have description and state, good enough.
All right! So, now to build our formula equal vlookup, A2 and the array that we want to use it over here press [ F4 ] comma 2 comma false.
Now, we have something working the other way but hey!
What if we don't want that Vlookup and our that Vlookup table embedded out there.
Well, we can turn this address back into an array very, very quickly.
Select those characters and press [ F9 ] and then just [ Enter ].
Copy that down we can actually now delete this array out here or this table out here and our formula continues to work.
All right!
So, very annoying thing that co-worker did there but using a couple of tricks are able to spin that table back, the other way.
Hey! I want to thank you for stopping by, see you next time for another netcast from MrExcel.
