How To Use Vlookup in Excel - And Have Fun Doing It
No Need to Fear, Excel Vlookup is Here!
As someone who continuously receives questions on how to use VLookup in Excel, I decided to write a fairly informative yet concisely entertaining tutorial on the subject (at least that's my intention).
In part one, we'll review the process of how to use the VLookup function within a simple Excel table. In part two, we'll watch an awesome video on the subject. The lesson is taught by Richard Post of Excel Learning Zone.com; he doe's an excellent job of explaining the basics.
By the time that you've completed this tutorial (I'll try to keep you awake...lol), you should have a fairly good understanding of how the VLookup function works. Since we'll be entering our functions directly, you can use any version of Excel to follow along. With that being said, let the data manipulating begin!
Quick VLookup Facts to Remember
- The VLookup function is used to find specific data within an Excel Table of a spreadsheet.
- VLookup can be used to find data on a separate worksheet tab beside the current worksheet (however, that capability is not reviewed in this tutorial ).
- The first column of data should be sorted in ascending order.
- VLookup has four arguments...
The Arguments Are: VLookup(lookup_value, table_array, col_index_num, [range_lookup])
Important Side Note: The VLookup example that used in this tutorial only works when all of the data is contained on the same worksheet.
Part 1 - How To Use VLookup in Excel and How it Works
The VLookup function searches for a value in the left-most column of a table; the value in the left-most column is the Look-up value. Upon locating that value, the function identifies the value to the right within the same row according to the column number that you specify (the column number is also known as the index number).
In order for the VLookup function to work correctly, your data should be sorted in alphabetical order by the Lookup value (again, the value in left-most column of the table). Ok, now that I've given you the pompous explanation, I'll explain in plain English:
Based on the Excel Table Below...
The following functions can be typed in any empty cell
If you entered the following VLookup function in cell C2:
The product name Video Cards would display in cell C2.
if you entered the following vlookup function in cell C3:
The product name Keyboards would display in cell C3.
=VLOOKUP(21001,A2:B11,2,FALSE) would return Video Cards
=VLOOKUP(21022,A2:B11,2,FALSE) would return Keyboards
=VLOOKUP(21096,A2:B11,2,FALSE) would return Sound Cards
=VLOOKUP(21199,A2:B11,2,FALSE) would return Wireless Mouse
Let's look at our first example in more detail:
- 21001 is the Product Code in the left column of the first row; this is the actual Lookup Value.
- A2:B11 represents the range of data from cells A2 to B11.
- 2 represents the column number to the right of the Lookup value; this is also known as the index number. If you wanted to identify a value in the third column, you would need to enter 3 as the index number.
- Entering the word FALSE at the end of the function assures that an exact match will be found. If you were interested in finding an approximate match, you would enter the word TRUE.
Part 2 - Learning Excel VLookup Visually
Ok, this may be a bit redundant but a little overkill never hurt anyone. As I mentioned in the introduction, Richard Rost does an excellent job of explaining the basics through his video tutorial. Enjoy!
You've Conquered the Basics of Vlookup
That's it; you're an official whiz on the fundamentals of how to use VLookup in Excel! What's more, you didn't fall asleep.
If you're interested in becoming a VLookup expert, or an Excel aficionado, I highly recommend that you learn some advanced Excel concepts. The Mr. Excel team, Bill Jelen and John Walkenbach, have been providing spreadsheet support for over 20 years. I've been using Excel for many years and can say without trepidation that these guys know their stuff. As a big fan of their work, I decided to shamelessly list a few of their books below.
In any event, If you have any questions or insights, fill free to take advantage of the comments section. Thanks!