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.
VLOOKUP WEEK 
March 27th, 2012 at 10:44
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.
March 27th, 2012 at 10:48
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!).
March 27th, 2012 at 10:53
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.
March 27th, 2012 at 11:00
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 🙂
March 27th, 2012 at 11:18
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)
March 27th, 2012 at 11:20
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!
March 27th, 2012 at 11:28
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.
March 27th, 2012 at 11:30
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!
March 27th, 2012 at 11:37
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.
March 27th, 2012 at 11:40
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.
March 27th, 2012 at 14:00
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
March 27th, 2012 at 14:06
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!
March 27th, 2012 at 15:06
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!
March 27th, 2012 at 15:58
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.
March 27th, 2012 at 16:53
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 .
March 27th, 2012 at 16:53
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.
March 27th, 2012 at 18:05
Love it!
April 2nd, 2012 at 16:24
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.
March 27th, 2012 at 17:06
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.
March 27th, 2012 at 17:11
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.
March 27th, 2012 at 19:10
=IF(ISERROR(VLOOKUP(A4,DOW!$A$2:F45,6,0)),””,VLOOKUP(A4,DOW!$A$2:F45,6,0))
March 27th, 2012 at 19:15
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.
March 28th, 2012 at 04:16
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.
March 28th, 2012 at 10:49
I Love the VLOOKUP because he saves me hunderts of hours of my time doing stupid list lookups and comparisons manually! 🙂
March 29th, 2012 at 14:12
I love, love love VLOOKUP! A coworker showed me VLOOKUP years ago and I’ve been a convert and VLOOKUP-Evangalizer ever since 🙂
March 30th, 2012 at 07:30
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
March 30th, 2012 at 09:30
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
April 2nd, 2012 at 16:22
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.
April 2nd, 2012 at 17:43
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.
April 2nd, 2012 at 17:49
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.
April 7th, 2012 at 13:44
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…
April 12th, 2012 at 14:21
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.
July 17th, 2013 at 10:36
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.