Excel is a powerful spreadsheet application that does more than just add up numbers and store numerical data. The Vlookup formula, for example, is one of Excel’s most powerful built-in functions and once you know how the Vlookup formula works, you will have a powerful way of sorting through your data to find information you are looking for at the touch of a button. The Vlookup function is an Excel function, so this tutorial will assume you have some basic knowledge of Excel and how to use the functions and formulas in Excel. For a great introduction to Excel 2013, enroll in the Excel 2013 – Getting Started course and join thousands of students who are learning to harness the power of Excel 2013. This course will teach you the basics of the application and how to open, setup and save a spreadsheet. The course includes an introduction to what a spreadsheet, worksheet and workbook are and how to work with them. It will also teach you about cells and cell references and how to work with cells. The course also includes a brief introduction to working with formulas and functions.
For this tutorial, we will create a set of fictitious sales data so that you can see how the Vlookup function works and how you can use this function in your worksheets. Our fictitious data looks like this:
Excel stores data in the forms of columns, or lists of data. The Lookup, Vlookup, and HLookup formulas allow you to sort through these lists of data to find information that meets the criteria we are looking for.
Finding the Vlookup Formula
There are two ways to access the functions and formulas in Excel. If you know the formula name and how the formula works, then you can type of the formula and requirements directly into the cell where you want the answer to appear. If you do not know the requirements of a formula, then you can select the formula from the list and a formula wizard will help you complete the formula. To access the formulas in Excel, you need to select the Formulas tab on the ribbon:
The Vlookup formula is part of the Lookup and Reference formulas, so select the “formulas” tab and then select “Lookup & Reference” and click on the “Vlookup” formula. This will launch the formula wizard:
Elements of the Vlookup formula include the “Lookup value”, the “Table_array”, the “Col_index_num” and the “range Lookup arguments. Let’s take a look at what these mean and how they work.
For a comprehensive course on Excel, sign up for the Excel 2013 For Dummies Video Training, Deluxe Edition and join thousands of students who are learning to use Excel 2013. This course offers 106 lectures and over 19 hours of video content designed to teach you everything you need to know about Excel 2013, including how to create and work with pivot tables, how to sort and filter data lists and how to share your workbooks via SkyDrive.
The Lookup Value
The lookup value is the value you are searching for in the list of data. Note: to search the data you need to arrange the data so that the value you are searching for appears in the first column of the data and that that column is sorted numerically. To search for a kind of fruit, use the formula wizard to select the kind of fruit you are searching for:
The formula wizard will update the field so when you select the field you will see that the formula will now search for “Apples” within the list of data.
Table Array Argument
Now that we have told Excel what we are searching for we need to tell Excel which table or list of data it must search. This is called the “Table_array” argument. To tell Excel to search your data, select this argument and then select the entire table of data you wish to search and then press enter to select that data:
Now we have told Excel what to search for and where to look for the information. Now we need to tell Excel what data we require from that item. In this example, we will use the function to find the price of that item.
We need to tell Excel what the column number is that contains the data we are searching for. To work out the column number, count from left to right and enter the column number that contains the data you want returned. In this case we want to return the Item price which is column number 3 in our table. So enter the number “3” in the argument wizard.
Excel allows you to search for an exact match or the closest match. In this case we want an exact match so you will enter “true” into the last argument. If you were looking for the closest match you could enter false and Excel will find the closest match instead of an exact match.
You will see that the formula wizard actually shows you what the answer to the formula will be. You can use this feature to make sure you have entered the formula correctly and that the formula is working properly. In our case you can see that this formula will return a value of $1.40 which is the price for apples so our formula is working correctly.
Once you are happy with the formula you can click enter.
Learn to use the Excel formulas and you will unlock the true power of Excel. The Vlookup formula will make finding information in large spreadsheets and worksheets as simple as one, two, three.
The Microsoft Excel 2013 Simplified course will teach you how to use the formulas and function in Excel to make your life easier. This course offers 110 lessons to ensure you get the most out of your Excel application. The course is suitable for both beginner and advanced users and you can return to lectures as often as you like when you need to learn how to use the functions and formulas in Excel.