April 30, 2019

The VLookup Hookup

Let me paint you a word picture.

It was a gorgeous summer Friday afternoon, on the verge of becoming a perfect beach weekend. Inside a converted bodega sat an exhausted, twenty-something non-profit administrator, on the verge of becoming a sobbing, snot-bubbled mess.

The email read: We need to see everyone’s information all in one place for Monday’s update meeting.

“Everyone” meant 100+ volunteers for a large-scale event, with their information scattered across Google Drive and Dropbox like stars along the Milky Way.

(Did I mention this whole affair was before we implemented Salesforce? Our database was a series of Google Sheets. Except when it was an Excel file in Dropbox.)

One spreadsheet tracked volunteers and their t-shirt sizes. Another tracked whether or not the volunteer had been background checked. A third tracked contact information and whether the individual had attended one of five available but mandatory training sessions. A fourth, fifth and sixth tracked other things. I can’t even remember what those data points were anymore.

I could see two options:

1)  Make my deadline, but miss my weekend.

2) Make my weekend, but miss my deadline.

I’m not proud of the terrible things I contemplated in that moment. Could I kick this cruel task to my interns for the weekend? Could I pretend my computer had exploded, that I hadn’t seen the email, that I’d contracted pink eye in the public swimming pool and was too contagious to go to work on Monday? Could I just…not do it?

If only I’d known about the secret third option that would let me make my deadline and my weekend, without terrorizing any interns. It looks like this:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])

I know, I know. It looks awful. But like bulldog puppies, sometimes the ugliest one in the bunch is going to be your best friend forever.

In short, the VLOOKUP (short for Vertical Lookup) function finds a value in one column and matches it to a value in another column.

The best part? The formula works the same way in both Excel and Google Sheets, so you can use it in both places (although not across those two platforms, sadly). Check out the video below to see how it works!

No lie, I think I use the VLOOKUP formula at least three times a week. What was once hours upon hours of work now happens in a matter of seconds.

...Guess I’ll need to find another task for the interns now.

(This post was originally published here.)