Mr. Excel got me on the lookout for ways I can leverage good 'ol fashion Excel formulas in my day to day work. Tonight I found my first scenario where Mr. Excel's advice came in handy. Here's the problem:
My boss sent me a spreadsheet with a few columns of data. However, one column was missing. He asked me to fill in that column by using data from database I had access to. There didn't appear to be an easy way for me to query the database and drop the results into the column. It was looking like I was going to have to run a query and manually place each value in the correct row in the column. Yuck!
Here's what the data looked like (scrubbed, of course) before I got started:
I started off by dropping the data from the database in another part of the spreadsheet. It's important that I kept that data as its own little table.
I did two things to the data. First, I lowercased all the names. I did this to make the data a little trickier to work with (you'll see how that works in a few moments). Second, I sorted the data in descending order. This step is required - things will break in strange ways if the data off to the side isn't sorted properly.
To actually fill in the missing column, I copied the following formula to each row in that column:
=VLOOKUP(LOWER(B5), $G$6:$H$12, 2, TRUE)
The key to this formula is vlookup, a function which will browse through a set of data and pull you back the appropriate value given a lookup key. In this case, the key to lookup is lower(B5) and the data set to check is $G$6:$H$12. The third argument 2 says which column to pull back when you find a match - the 2nd column in this case. The final argument, TRUE is there for reasons I can't explain. Without it, things don't work properly.
Here's the final result:
This spreadsheet is nice and maintainable. If the database values were ever to change, I could drop in a new lookup table off to the side and the sheet would automatically correct itself.
This was fun! I'm so keeping an eye out for more ways I can leverage Excel.