Vlookup Personal Projects

December 5, 2016 4:37 AM

I am looking for an every day project to up my Excel Vlookup game. Any ideas would be useful.

Hi,

I am currently 'coasting' in my use of Excel at work and I want to start using lookups. I have put my name forward for a project which will require me to use it but I want to be able to make it second nature before I get started, otherwise I will shit myself mightily and slow the project down. Do you know of any personal projects I could undertake that would require me to run a Vlookup on a daily basis?

I got much better at using Microsoft Word through creating diary entries made to look like blogs (for personal use) and doing that every day really upped my skill level. I want to do the same with Excel but I can't think of how.

I will need to have access to a reasonably large stream of data that I can use in Excel and then a reason to run a lookup every day. It does not have to be an exciting project. It can revolve around any sort of data from stamps to types of salami. I don't care. It just needs to be data that changes regularly.

Thanks.

posted by ihaveyourfoot to Computers & Internet (8 answers total)

Let me be the first to say that if you're training on something new-to-you, don't poison your brain with VLOOKUP. Jump straight to the much more robust INDEX/MATCH.
posted by komara at 5:34 AM on December 5, 2016 [16 favorites]

An easy, clean, relatively useful data set is local weather; wunderground.com has hourly, daily, weekly and yearly data available. NYC data, change to your locality

Using their I would do sets of vlookups for the following:
col 1. for a specific subset of dates (maybe 2 months); pull in the max temp via a v-lookup
col 2. determine which days are Wednesdays (use text() function on a date string)
col 3. For each wednesday, that is higher than 50 deg, what was the visibility on that day (if statement, then vlookup)
col4. replicate with more complex functions (index, match etc) (Vlookups are only useful to a certain extent, and you might as well learn some other ways of finding things)

I threw in an if() function in there, as quite frankly, I find that I use vlookups conditionally. You also should familiarize yourself with the isna() function, which nested in an If() with a vlookup is quite useful.

For practical purposes, I track weather vs. mood, sleep quality and weight (I started a sleep diary about 2 yrs ago after a severe bout of insomnia). I now know that it's not all in my head that I sleep better when it's cold, but it's actually a higher correlation to low/high pressure. and that if I'm sleeping more, my weight drops/workouts seem more effective.

Other potentially useful uses of weather data: weather vs. commute time, weather vs. mood, weather vs. productivity...
posted by larthegreat at 5:38 AM on December 5, 2016 [1 favorite]

I would also suggest using an index/match.
It's a more flexible, faster and safer alternative to vlookup.
But now that two of us have said that we probably don't need anyone else to suggest it. :)


Data.gov.uk has a ton of data in csv form. It has stuff like, all public transport data to how many people have been fined for not cleaning up after thir dog.
Here is someone discussing some of them.

You could probably have a lot of fun (if manipulating datasets in excel is your idea of fun, which... if not why not) by smushing together wildly unrelated data sets.
posted by Just this guy, y'know at 6:10 AM on December 5, 2016 [2 favorites]

I also find INDEX/MATCH to be much more robust (and easy to use!) than a VLOOKUP. One personal project where you could use either of these would be to categorize your transactions on your debit/credit cards, ie if the transaction says "SHELL" your VLOOKUP would return "GAS". This may involve playing around with a LEFT or RIGHT function depending on if you visit multiple locations of one chain.
posted by sorrel at 6:11 AM on December 5, 2016

I downloaded a bunch of yearly name data from the social security website and used it to see names that were rising and falling in rank over time. I used VLookup for this.
posted by vunder at 7:18 AM on December 5, 2016

If you want something more personal, you can download transaction data from your credit card or Mint-like websites. Create yourself a dashboard that's organized by category, month, whatever other columns you have and then use vlookups or index/ match to populate it.
posted by oryelle at 5:13 PM on December 5, 2016

Response by poster: Thank you all very much! This is great!

INDEX/MATCH noted and a special thanks to those who provided links and project ideas!!!

Is it wrong to be excited about this?!
posted by ihaveyourfoot at 10:09 AM on December 6, 2016 [2 favorites]

It is not. Doing a particularly neat or clever bit of excel-ry is very satisfying.
posted by Just this guy, y'know at 10:51 AM on December 6, 2016 [1 favorite]