Okay if you read this blog you’ll know I’m more of a DB geek than an Excel user but I’m getting tired of every time that I want to do a quick sub-out of data having to load not one but two files into the DB and then write the query to join the two and get what I need. It’s a great way to go but awfully time consuming if it’s not going to be built into a SSIS pack or something of the sort.
So that brings us to the old standard Excel. Actually Excel is awfully handy on the export side of things but using it to import data from a DB or even flat file.
In this case we’ve got two sheets representing two tables with a common key.
- =VLOOKUP(F2,States!A$2:B$38,2)
- The $ keeps the range in the middle from moving when you populate down.
- In this case this is done in G2 where F2 is being looked up.
- The range is on a second sheet from A2 to B38 where items in A relate to F and the B value will be returned for G.
- Remember to have the columns in the same order to pull this off.
Just to note, if you are looking at using a field with a tilde, you have to escape it, which looks like this:
=VLOOKUP(SUBSTITUTE(B2,”~”,”~~”),Sheet1!A$2:B$234,2,FALSE)
About Me