If you work in an office doing menial number crunching on huge amounts of data, the biggest secret to success is becoming adept at Excel and not telling anyone. It’s what I do. Someone comes to my desk and says: “Hey, sorry to do this to you, but could you go through 2,000 rows of poorly formatted data and tell me which ones have been requisitioned but not shipped? I know it’s short notice, but could you have it done by Friday?” I act like it’s a huge pain, then I whip up an Excel formula that does it in five minutes by Monday afternoon then play Tetris until Friday. Okay, I don’t really do that. But in all seriousness, using Excel to perform detailed tasks with data is absolutely essential to my day job. Doing things manually, it’d take me eight years and my eyes would glaze over, and I’d make mistakes. Excel lets me do things faster and more accurately. On that note, if I could take just one Excel function to the prom, it’d be VLOOKUP. I hadn’t heard of VLOOKUP until I had a couple of years of Excel experience under my belt. I really wish I would’ve learned to use it sooner. What VLOOKUP does is pretty simple. But used creatively, or in conjunction with other functions or formulas, VLOOKUP is an absolute powerhouse. Let me explain. If you have a massive existing spreadsheet of data, VLOOKUP takes a known value—like a part number—and finds an unknown value for you—like the price.
VLOOKUP has four arguments: VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])
lookup_value: This is the value that you want VLOOKUP to matchtable_array: This is where you want VLOOKUP to check for your datacol_index_num: This is the column where the data you want to fetch is.range_lookup: Set to true to allow approximate matches. For example, if you are looking for 90 and there is no 90, it’ll give you the result for the next closest number that does exist: 83. I usually leave it false, so I get an exact match.
So, for looking up a part number it goes a bit like this: Me: “Hey, VLOOKUP!” VLOOKUP: “Yes, Jack?” Me: “I want the price of a part number.” VLOOKUP: “Ok, what’s the part number?” [lookup_value] Me: “B002.” VLOOKUP: “Ok, where’s the data?” [table_array] Me: “Columns A through C.” VLOOKUP: “Alright, counting from the left, which column is the price in?” [col_index_num] Me: “3rd column.” VLOOKUP: “And do you want an exact match of the part number, or is approximate okay?” [range_lookup] Me: “An exact match.” VLOOKUP: “Ok, it’s $2.00.” Me: “Nice. Can you do it again for this list of 100 part numbers in this order?” VLOOKUP: “Yes, give me one second… done.” Me: “You’re the best. You wanna go grab a hamburger sometime?” VLOOKUP: “I’d love to.”
So, that’s enough to get you started on VLOOKUP. Once you get the hang of it, you’ll start using it all the time. I highly recommend printing out this handy reference sheet that Microsoft put together for VLOOKUP enthusiasts. The image at the top of this post comes from the refresher PDF. Click here to download the PDF. It has a description of all the arguments as well as some examples.
After you’ve used VLOOKUP for a while, let me know how you like it in the comments. I’d love to see the formulas you come up with using VLOOKUP. But I far prefer index/match. Also, format your data as a table. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
