I Would be Screwed at my Job without VLOOKUP! (Ode to VLOOKUP)

I formerly worked as the Manager of Dealing with Bad Data.

OK – maybe that wasn’t my real job title, but it is what I did day after day. I was the guy who knew how to use a bad mainframe tool to extract imperfect data sets and then coax useful information out of them in a spreadsheet. I used VLOOKUP constantly to join this data with that data. Why didn’t we just get all the data in one download? Because this was 1989 and it just wasn’t possible with the $100K 4GL tool that my company purchased. Of course, using a $299 spreadsheet and VLOOKUP, everything was possible.

I held this position for 10 years. They gave me other job titles and promotions, but, I was still doing the same thing: dealing with bad data. I overheard someone ask the I.T. manager if we ran our company on Oracle or SAP. He had to admit that we pretty much ran the company on Excel. He probably didn’t mean that as a compliment, but I was proud of his statement.

When I announced VLOOKUP Week on Facebook, one of the comments was from Erica:

“I use vlookup and pivot tables countless times a day, I would be screwed at my job w/out it.”

Erica and thousands of others and I could not live without VLOOKUP. If you are one of those thousands, I invite you to comment on this post with a few lines about how you use VLOOKUP. Feel free to be creative. Be passionate. Write a little love note to VLOOKUP. You don’t need to drop into verse or rock out a haiku, but let the world know that “you have VLOOKUPs and you know how to use them”.

For our door prize today, we have a VLOOKUP Week t-shirt and another copy of Paul McFedries’  Formulas and Functions Excel 2010. To be entered in the drawing, post a comment to this thread with your Ode to VLOOKUP. Enter by 6AM on Wednesday to be eligible.

About Bill Jelen

Author of 41 books about Microsoft Excel. View all posts by Bill Jelen

33 responses to “I Would be Screwed at my Job without VLOOKUP! (Ode to VLOOKUP)

  • jpaules029

    VLOOKUP and pivot tables make me look like an Excel guru. I seriously believe that if not for learning them I would be working part-time retail just to have a paycheck coming to me every week.

  • Flint C

    Vlookups save my bacon on a daily basis. I am the financial manager for 12 different entities, all of which use different systems. To further cloud the issue, some of our entities handle partial billing for the other groups.

    I utilize vlookup to quickly and easily differentiate data between the different entities. Now, instead of having to pour through thousands of lines of data, I am simply maintaining a crosswalk file with about 100 names. Saves me countless hours in data collection, and allows me to actually spend my time doing what I should be doing: Data analysis.

    Even better, my organization’s parent company has adopted my methodologies as best practice! So, now I have the pride of knowing that my vlookup formulas and formats are used nation wide!

    Thanks, vlookup (and Bill too!).

  • ute-simon

    I exactly remember the moment when I learned VLOOKUP nearly ten years ago now: We held a large Office training event, I was helping with administrative tasks, and was handling participant data. One night around 1 a.m. I had to combine data from different tables to prepare for the next day. I had heard that there exists a function for that, but also heard that it were a very complicated one … So I asked one of the the Excel trainers who were still hanging around, “I think I need that VLOOKUP function for this – can you please explain it to me?” And so I learned my first bit of VLOOKUP in the middle of the night – and never forgot it ever after. Today I am teaching Excel courses myself, and help to spread the knowledge of this important function.

  • Dualvba

    I love vlookups, I honestly don’t know what I would do without them, they have saved me using various filters trying to match up data in different worksheets. I have also tried hlookups too, but not as much🙂

  • Michael Pennington

    I can’t believe I am doing this, but here is my Haiku:

    Oh how I struggled
    Til the V Lookup I found
    Excel whiz kid now

    There is no limit to the depths of depravity to which I will sink for free Excel swag :o)

  • Kip J.

    VLOOKUP, VLOOKUP,
    How do I love thee?
    Let me count the ways …

    The tens of thousands of records, nay,
    The now 1,048,576 potential records per sheet,
    A full 16,384 potential fields wide,
    Would be a mountain of bad data
    Too high to climb.

    My bosses in the past couldn’t understand,
    Perhaps enjoyed the long hours I slaved
    Over the tedious projects from their hands,
    Rushing to meet their impossible deadlines —
    Only to start climbing that mountain summit again
    With their next assignments,
    Due before the end of the business day …

    Whether tracking statewide underground facility excavation violations,
    National subcontractors’ software bug fixes,
    Or hospital CFOs’ every financial metric desires,
    My eyes grew weary and my fingers waxed numb
    With every scroll up and down those endless records,
    Scanning for that needle of a matching value
    In a towering haystack of a list —
    Over and over and over again.

    Sorts and filters would help,
    Ctrl+F would aid my quest for a time,
    But the whistle blowing at the end of every workday
    And every workweek
    Would bring another excuse from my mouth
    For why I needed just a little more time,
    Just one more day …

    VLOOKUP, VLOOKUP,
    Where have you been all my life?
    You’ve saved my world,
    You’ve saved my career,
    You’ve given my time back to me!

    Projects are completed ahead of schedule —
    Shhhhhh! —
    With impeccable accuracy,
    With less stress,
    With bosses more satisfied,
    With more enjoyment
    And more recognition for me.

    More time at work to read
    And strengthen skills sets:
    Pivot Tables,
    PowerPivot,
    Slicers,
    VB and Macros,
    Excel 2010 In Depth.

    I’m such a nerd … but I really love working with Excel!

  • helene lukey

    Vlookup are right behind pivot tables in my favorite excel things.
    My job OFTEN requires mixing tow datasets into one. Without vlookup life at work would not be very much fun.

  • Kip J.

    Excellent VLOOKUP WEEK Video #1 from Mike “ExcelisFun” Girvin … great introduction for newbies as well as advanced VLOOKUP applications for the more experienced user. 26 easy-to-follow lessons.

    I’ve got my 12- and 13-year-old nephews learning about VLOOKUPs and HLOOKUPs … good job, Mike!

  • Ken Hudson

    I work in Finance for a large federal government (US) agency. I consider myself an intermediate Excel user but, compared to the majority of the thousands of others in my field, I am probably quite advanced in their eyes as I have developed scores of apps to save time. VLOOKUP is certainly one of, if not the most, useful functions available to the “average” user. Every time I post a solution to a problem and use VLOOKUP, I get a flock of responses from colleagues who didn’t know about it. And this is just the “plain vanilla” VLOOKUP stuff, not the more advanced ones posted here this week. Folks just don’t know enough about what Excel can do to spur them to ask for assistance solving problems.

  • JR

    Vlookup was a life changer for me – making my job MUCH easier and opening up many possibilities for new things. I cannot imagine what I would do without it.

  • Alan Jones

    Vlookup is the first function that helped me realize the power of Excel. I realized then that if I can do some of the stuff with Vlookup then next I might be able to do something else cool.

    Vlookup is just plain FUN!!!

    Alan

  • Elaine

    I still remember 10 years ago when my college professor introduced vlookup and pivot table to us in class. He came to the class, opened up the web browser on the computer and got on to a job board website. Keyword search he used: vlookup. A couple of job postings popped up from his search. He did the same for pivot table.

    I was so thankful that he mentioned the importance of knowing these two Excel functions to the students. Since then, I have picked up some Excel books to continue learning. The simple vlookup is not that intimidating after you get through it. Index and Match became next. Knowing how to combine different Excel functions make you a guru! Honestly, I still don’t know all the advanced ones mentioned here. However, without knowing basic vlookup, I wouldn’t be employed or stayed employed!

    Thank you Mr. Excel and ExcelIsFun for the videos; you two have made learning Excel more interactive and so much fun!

  • Jack

    I am an IT Project Manager / Business Analyst with 20 years experience working with SAP, including programming in ABAP. However, in my current company, only the outside consultants are allowed to program. So, my only option is to extract data files (SE16N in SAP) and pull them into Excel.

    VLookup, Pivot Tables, and VBA are what help keep me sane!

  • Data fixer

    I too am a manager of bad data. They’ve changed my job title a few times to try and put it in a good way, never wanting to call it what it really is. Working on several mis-matching, disparate systems has meant that extracting data and using VLookup is the only way to get anything done.
    In the end I’ve built a kind of excel data warehouse – data is imported and VLookups are used to collate the data into a single tab. That way I don’t have to repeat the same work over and over. I can automatically see gaps and differences and get the whole picture in one place.

  • Ray Double U

    There is an old trick worth tellin’,

    Report for items not sellin’ –

    Database me shook up

    With only V Lookup,

    Thanks to the other “Bill” – Jelen .

  • Ben Niebuhr

    PUBLIC WARNING:
    Vlookup is the gateway formula. Learn how to use it, and you can easily be drawn into a life as a data junkie. What starts as a innocent vlookup can quickly escalate to a nested if, into an array, and hopelessly addicted to PowerPivot.

    You have been warned.

    • ISAAC PISORS

      Stay far away from array formulas, they come with too much of an overhead cost, are too difficult to modify. In my opinion people who use array formulas – that’s one (out of a number of possible) symptoms of people who are trying to over-use formulas, instead of just using helper columns, Access where it’s warranted, or VBA……All 3 of which are actualLY EASIER to use (create, look at, and maintain) than these super-complicated array formulas.

      I HATE array formulas more every year I work in Excel. There are many ways to get to the same place, easier.

  • Eddy Tse

    Ever since I started using VLookup and PivotTable in my work. They seem to take over everything I do with Excel. There is not one day gone by without using either one of them. I love them so much that I have became my agency’s Excel guru.

  • Neil

    I teach Excel classes almost weekly, and it never fails – VLOOKUP and Pivot Tables are the things that make people’s faces light up. They spend years and years in fear of these faceless things, and then I show them exactly how easy it all is – I can just tell that their jobs will not be the same tomorrow.

  • EFS

    =IF(ISERROR(VLOOKUP(A4,DOW!$A$2:F45,6,0)),””,VLOOKUP(A4,DOW!$A$2:F45,6,0))

  • Tom McManus

    I taught myself VLOOKUP when I needed a way to match my technicians work product with these huge data dumps I used to get. The data dumps only provided the 3 character ID that the techs used when logged in. I used VLOOKUP to match that ID with their real name. I would then match that to their supervisor and on up the reporting chain.

    It made me look like a star that I was able to massage a huge amount of data into a usable form.

  • Yvonne Lee (@Vontweeter)

    I love vlookup so much I’m coming out of my social media shell to publically say how much I love vlookup! I don’t think I’d love Excel and analysis work as much if it weren’t for features such as vlookup.

  • Andreas

    I Love the VLOOKUP because he saves me hunderts of hours of my time doing stupid list lookups and comparisons manually!🙂

  • CKemppel

    I love, love love VLOOKUP! A coworker showed me VLOOKUP years ago and I’ve been a convert and VLOOKUP-Evangalizer ever since🙂

  • music43

    Here goes nothing

    An Excel user pattern,
    is clearly visible.
    Ninety nine in one hundred,
    know no formulas at all.

    Soon they venture out,
    with baby steps at first.
    Then they learn the function SUM,
    and develop quite a thirst.

    Progression carries on,
    your pace it gathers steam.
    With IF and VLOOKUP,
    soon members of the team.

    Before not very long,
    you’re the local Excel hero.
    Except one day the data’s bad
    and VLOOKUP returns a zero.*

    After checking in the help,
    the problem you highlight.
    A value to the left,
    VLOOKUP’s kryptonite.

    Forums are full of people,
    a global expert nation.
    They’ll teach you INDEX/MATCH,
    the tag team combination.

    I like many of you
    have travelled in this way
    but since I met the tag team
    from VLOOKUP I’ve walked away…

    * Poetic licence – would return an #N/A

  • Oli

    An Excel user pattern,
    is clearly visible.
    Ninety nine in one hundred,
    know no formulas at all.

    Soon they venture out,
    with baby steps at first.
    Then they learn the function SUM,
    and develop quite a thirst.

    Progression carries on,
    your pace it gathers steam.
    With IF and VLOOKUP,
    soon members of the team.

    Before not very long,
    you’re the local Excel hero.
    Except one day the data’s bad
    and VLOOKUP returns a zero.*

    After checking in the help,
    the problem you highlight.
    A value to the left,
    VLOOKUP’s kryptonite.

    Forums are full of people,
    a global expert nation.
    They’ll teach you INDEX/MATCH,
    the tag team combination.

    I like many of you
    have travelled in this way
    but since I met the tag team
    from VLOOKUP I’ve walked away…

    * Poetic licence – would return an #N/A

  • ISAAC PISORS

    I’ll be happy to sing the praise of VLOOKUP, but I think the most interesting part of my contribution/comment is more of a detraction:

    1) most people get too obsessed with vlookup, as if it is the be-all and end-all of Excel. I’ve actually seen job postings that state, “Need EXPERT in Advanced Excel functions – i.e., VLOOKUP and Pivot Tables”. Well, vlookup takes about 7.3 seconds to learn, so considering that the be-all and end-all of excel is ridiculous. I agree with the last person – try Index and Match for a combination much more powerful, and a bit more complicated to use as well.

    2) most of the people using vlookup to such a large extent need to learn when to stop using Excel and when to start using Access. I also showed many COMMONLY SHOWN symptoms – symptoms of “treating excel as if it is a database”, and one of my major symptoms was that I used vlookup for everything and its brother, simply because I was ignorant of using Access queries.

    3) being in love with vlookup requires you to have a very solid handle on data cleaning and data transformation. The dreaded N/A (or just a blank space or some other very ugly and confusing result) can happen because you didn’t clean the data you’re “reading” well enough.

    At the end of the day, being able to write fairly simple and short VBA routines to clean data and populate results ACTUALLY BECOMES EASIER FOR YOU than dreaming up complicated formulas……Yes believe it or not it does. Try writing 15 nested IF statements in an Excel formula…….now try writing it in vba code with Select Case or just true nested IFs..you’re realize that the VBA is far, far easier to read…..look at….follow…understand……..AND MODIFY.

    • Bill Jelen

      Isaac:

      I’ve actually posted the exact job posting shown in your point 1.

      For point 2, until they include Access in Office Standard, it simply isn’t an option.

      • Isaac Pisors

        We can agree to disagree then🙂 There’s nothing wrong with posting that job posting, of course – that’s completely fine. I don’t doubt that you’ve posted it, I still see it as a symptom of the point I’m making.

        I still stand by my points that over the years, my observations have been:

        1) too many people incorrectly view Excel as being a database, and then worse yet, what happens when you get to Access is that you see it as “excel on steroids”. All in all, it’s a completely fundamental misunderstanding of RELATIONAL DATA vs. NUMBER CRUNCHING.

        2) Most of my interactions with people who i am helping with their Excel questions do have Access; they work for companies who will easily provide Access if asked … It’s more a training issue on what each application is actually suited for.

        All in all, vlookup IS a great tool – and Yes I do use it, and Yes I do like it 🙂 But its over-use is a symptom of a known, larger problem with misunderstandings about Excel which seem cool for a while, but if the person wants to truly grow in their understanding of working with data, eventually you have to take a more careful view of things.

  • debdarsan

    VLOOKUP is something you do everyday – excel user or not, like when meeting somebody interesting🙂 It is a pity, if you get N/A in return.

    You get VLOOKUPed by others too…

  • EXCEL PRO

    With out VLOOKUP we are are in the past.
    Just Imagine doing some work that needs VLOOKUP but doing it with your own hands. VLOOKUP helps to grow the productivity for people or the companies.

  • Jenny Halasz

    Obviously very late to the vlookup party, but as an SEO, I use it daily and find it so much more useful than pivot tables.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: