Vlookup – Cheat Sheet for Vlookup in Excel

When you’re working with large workbooks that contain thousands of rows of data, then finding information can be worse than searching for a needle in a haystack. Luckily Excel 2013 comes with built-in lookup functions like Vlookup and Hlookup that makes finding that needle really simple. This tutorial will teach about how the Vlookup function works and how you can use the Vlookup function to find any data within your worksheet. If you are new to Excel, consider enrolling in the Excel 2013 for Dummies Video Training, Deluxe Edition course now and join over one thousand students who are learning to harness the power of Excel to manage and sort through their data.

How Functions Work in Excel

If you are new to Excel, then you may not know how to take advantage of the amazing built-in functions that Excel offers. Excel 2013 has made using the built-in functions really easy for new users. The Excel 2013 ribbon contains a “formulas” tab that has made adding functions and formulas to your worksheet really simple.

The formulas tab not only gives you access to the built-in functions and formulas, but Excel has actually arranged the formulas in terms of what they do, making it really easy to find the functions you are looking for. If you choose the function using this menu, then Excel also opens a formula wizard that will literally walk you through how the function works.

As an example, let’s take a look at the Vlookup function and how this function works.

What the Vlookup Function Does

The Vlookup function is designed to allow you to search data within a worksheet or workbook to find information that is related to the data you enter. As an example, imagine you work for a computer company and the company’s item spreadsheet that records item sales, contains a few thousand items. As a salesperson, you may need a way of finding item prices quickly but searching thousands of items for a price may be too time consuming. The Vlookup function is designed to look up information based on specific information you enter for the search. So let’s take a look at how the Vlookup works. We will use the following data for the tutorial:

Where to Find the Vlookup Function

Based on our tutorial data, we need to first find the Vlookup function. The Vlookup function is a lookup and reference function, so if you click the formula tab and then click on “Lookup and Reference,” you will find the Vlookup function. When you click on the function the function wizard automatically opens to help you enter the function arguments.

Function arguments are things Excel needs to know to use that particular function. In terms of the Vlookup function, Excel needs to know:

·         The Lookup Value

·         The Table Array

·         The Column Index Number

·         The Range lookup

The Lookup Value

The lookup value refers to the value you are looking for in the table. In our case, we are looking for a particular item so our lookup value will be a PC or laptop or whatever we are looking for. For the tutorial, we will look for an iPad to use Vlookup to look for the price. To look for the item, type the item name in the wizard or click the box next to the field, and then select the item from the worksheet itself. Your wizard should look like this now:

The Table Array Argument

The next thing Excel needs to know, is what information it should search. Table array is just a fancy way of telling you to select the information you want Excel to search. To enter the table array, select the small square at the end of the field and then select the information you want Excel to search:

Once you have used your mouse to highlight the whole table, press enter and you Excel will go back to the wizard:

The Vlookup Column Index Number

Now Excel needs us to tell it what information we are interested in, if it finds the item we are looking for. We will ask Excel to return the price if it finds an item called “iPad”. The column  number index is a number that you will use to represent the column that contains the information we are looking for. You need to count the column number that contains this information. In our case, the price of each item is contained in column 3 so we just need to type “3” into the field:

The Vlookup Range Lookup Argument

Now all we need to do is specify the range lookup argument and Excel will have all the information it needs to find the item we are looking for. The range lookup argument is especially handy if you are not sure what the precise name of the item is. This argument allows you to tell Excel if it is searching for the closest match or if it must search for an exact match. The range lookup allows you to enter “TRUE” or “FALSE”. If you enter TRUE it will find the closest match and if you enter FALSE it will only find an exact match.

For the purposes of this tutorial we will ask Excel to find the exact match, so we will enter “FALSE”

Now that you have completed the wizard, click enter and Excel will present you with the answer to your search:

Use Vlookup to Search Thousands of Records

The lookup functions in Excel are a powerful way to search large workbooks for almost any information you need.

Enroll in the Microsoft Excel 2013 Training Tutorial course now and learn to harness the power of the various built-in functions in Excel. This course offers over 226 lectures and over 18 hours of video content designed to teach you how to become an Excel master. The course includes three classroom instruction manuals to ensure you have access to all of the information you need to really harness the power of Excel. The course will take you from beginner to advanced user and includes lessons on using 3D formulas, auditing worksheets, creating charts and using advanced features like pivot tables and charts.