Vlookup examples in Excel 2013 – Make searching simple

Excel has a number of built-in formulas and functions to help you store, search and analyze your data. If you are working with large worksheets, then finding specific data within that worksheet can prove quite difficult. This tutorial will show you how to use the Vlookup function to find data quickly and easily. We will use the Vlookup function to lookup specific data and then we will create a function to allow others to find data quickly and easily by selecting an item. This tutorial contains references to function like named ranges. For a great introduction to Excel, check out the Excel 2013 For Dummies Video Training, Deluxe Edition course. This course offers over 106 lectures and 19 hours of content designed to take you from beginner to Excel master. You will learn how to launch and navigate Excel worksheets. You will learn to add data and how to use AutoFill to make adding data really simple. The course will teach you about the various Excel Formulas and how to use them in your worksheets. Once you are familiar with simple tasks, the course will teach you about advanced techniques including working with pivot charts and tables, and sorting and filtering data.

How to Find Vlookup

The Vlookup formula can be found on the Formulas tab, listed under the “Lookup & Reference” section:

Excel allows you to add formulas to your worksheets in two different ways. You can type a formula directly into a cell using the “=” sign and then by typing the formula name:

As you can see above, Excel guides you on how to enter the formula correctly. Or you can add a formula by selecting it from the formula menu. If you select it via the menu, then Excel automatically opens a wizard to help you complete the formula.

We will use the formula wizard to lookup data in our worksheet. Let’s assume you need to lookup what sales were for Cannoli’s for the day. To use the Vlookup formula, select the Vlookup formula from the “Lookup & Reference” tab on the formula menu. The following wizard will open up:

With Vlookup we need to tell Excel what value we are looking for, where to find that value, what corresponding value it must find for us, and whether we are looking for an exact match or the closest match.

The things we need to add to the formula wizard are therefore:

“Cannoli” – because we want it to find Cannoli’s. Table: A1:F5, because we need to search our table that contains the data. The column that contains the data is column number “4” and we want it to find an exact match so the last field we need to enter is “FALSE”.

This is what the spreadsheet will look like once you have entered the above information:

Let’s make this formula a little easier to use for other people who may be using the worksheet. Instead of typing in the formula each time, we will create a dropdown list of items to choose from and then display the sales depending on the item the user chooses.

Add a Dropdown List

To create a dropdown list for our items, we need to define the items as a range. Select the items and then right click and select define name. Name the list “Items” and click OK.

Be sure to validate the data for the list. Now we have a list to choose from for our Vlookup formula.

Now we can create the Vlookup formula based on the item the user selected.

If you are not sure how to add a dropdown list, then sign up for the Learn Microsoft Excel 2013 course and learn to tap into the power of Excel. This course includes 102 lessons and over 3.5 hours of content that will help you master Excel 2013. You will be able to follow the video lessons to improve your Excel knowledge and skills and by the end of the course you will be capable of using Excel in a number of different business settings.

Add Vlookup Formula

So instead of typing in “Cannoli,” we will ask Excel to use the value that the user selects. So the Lookup value will be the value in cell C9. We are still looking up the data is the same table so the table array value is still A1:A5. And we are still looking up the sales for the day so the column is still 4. This is what your formula wizard should look like now:

Let’s add another field to our formula so we can update sales amount and price using the same selection:

Add the Vlookup Formula to Look Up Price

Instead of the formula wizard, we will add a Vlookup formula to look up the price for us. Take a look at the formula below:

It contains the same information as the wizard, but we have skipped the wizard and typed the formula into the cell. Note that it is looking for the item the user selected in cell C9. It is still searching the same table, but this time it is finding the value in column 2 which is the price of the item.

This is what the completed worksheet looks like if you select “Cupcakes” from the dropdown list:

And it looks like this if the user selects “cakes”:

Formulas Make Data Analysis Simple

Learning to work with the various formulas and functions available in Excel will make working with data far simpler.

Enroll in the Microsoft Excel 2013 Beginners/Intermediate Training course now and join over two thousand students who are learning to harness the power of Excel 2013. The course contains over 58 lessons and 14 hours of video content designed to teach you to master Excel. It will teach you everything from data entry and cell formatting, to tips for using a mouse or touchscreen. You will learn to work with graphs, charts, pivot tables and learn to use custom templates and designs. The course will also teach you how to share your work via SkyDrive and the course also includes bonus exercise files to ensure you become an Excel 2013 Pro.