These instructions will walk you through the process of using VLOOKUPS in Microsoft Excel 2016 by the use of two examples.
Step 1: Opening Microsoft Excel 2016 (Windows 10)
On your Desktop, click the Start Button in the bottom left hand corner.
Step 2: Opening Microsoft Excel 2016 (Windows 10)
A menu of all the applications on your computer will appear, scroll through the different applications until you see “Excel 2016”. Click it to open a new Excel document.
Step 3: Opening Microsoft Excel 2016 (Windows 10)
A menu will be displayed asking you which type of template you would like to create. For this demonstration we will be starting from scratch, so double click “Blank Workbook”.
Step 4: Opening Microsoft Excel 2016 (Windows 10
A blank spreadsheet will open and you are now ready to start preparing the given data.
Step 5: First Example With VLOOKUP
Our first example will be a simple example to demonstrate the function of VLOOKUP.
Let’s create some simple data to work with.
1. Select cell A1 and enter “Part Number”
2. Select cell B1 and enter “Part Name”
3. Select cell C1 and enter “Part Price”
Step 6: First Example With VLOOKUP
Now we will create the data itself.
1. Return to column A.
a. In cells A2, A3, and A4, enter three part numbers.
2. Move to column B and create three part names.
3. Finally, start at cell C2, and move down to create three part prices.
4. Click the dollar sign on the number format toolbar to enable data to be shown as price values
Step 7: First Example With VLOOKUP
You should now have complete data, like the first image.
1. Select cell E2 and type “Number.” Select cell E3 and type “Price.”
2. Select cell F2 and enter one of the part numbers, for which we will look up the price.
a. We chose to look up the price of part number 1003.
Step 8: First Example With VLOOKUP
Now to execute the formula:
1. Select cell F3. This is where we will use VLOOKUP.
2. Type “=VLOOKUP(“ to start the formula wizard.
Step 9: First Example With VLOOKUP
As you can see above, Excel lets you know what values you will need to provide.
1. “Lookup_value” is what you are using to identify the item you are looking for. Select cell F2 in this case, as we are looking up the item using the part number.
2. The formula will take “F2” as the lookup value. Type a comma to move to “table_array.”
3. Now, click and drag the Row A header from A to C. This tells Excel where our data is.
Step 10: First Example With VLOOKUP
1. Type a comma to move to the third argument in the formula.
2. Because we are interested in the part’s price, type “3” and then another comma. We type “3” because we what the formula to bring back the information from column 3.
3. Now, type “false” because we want the part number to match exactly.
Your formula should look like the one above:
4. Press enter, and F3 should be populated with the price of the Flux Capacitor, $2500.
Step 11: Closing the Excel File and Importing a New One
Click on “File” located on the top left corner of Excel.
Step 12: Closing the Excel File and Importing a New One
Locate and click on “Close” from the drop down menu.
Step 13: Closing the Excel File and Importing a New One
Click “Don’t Save” on the popup box.
Step 14: Importing Data/Tables
Download the file attached to this step.
This Excel file is the sample data used in our second example.
Step 15: Importing Data/Tables
Click on “File”, located on the top left corner of Excel.
Step 16: Importing Data/Tables
Locate and click
“Open” from the drop down menu, then click “Browse.”
Step 17: Importing Data/Tables
Navigate to the location where you saved the Data/Table.
Once the Data/Table is located, select it and then click “Open”.
Step 18: Importing Data/Tables
Click on “Enable Editing” located at the top of the screen, under the menu bar.
Step 19: Importing Data/Tables
The Data/Table is now imported. You are ready to begin setting up VLOOKUPS.
Step 20: Second Example With VLOOKUP
The following are instructions on how to connect two tables from a database for analysis. In this case we would like to find out what the name of the customer is who placed the order.
1. Upon opening, you will find two sample database tables in the two tabs below. Click on the Order Table Tab.
Step 21: Second Example With VLOOKUP
In column G row 1, type Customer Name.
Step 22: Second Example With VLOOKUP
Now we will begin to execute the VLOOKUP formula.
For a video explanation regarding the formula process, please see step 28 at the end of these instructions.
Select cell G2. Type “=VLOOKUP(“. After typing, click on cell F2 which will be your lookup value.
Step 23: Second Example With VLOOKUP
Type a comma after clicking your lookup value to proceed to your next parameter.
Step 24: Second Example With VLOOKUP
Click on the Customer Information Table tab, and then click and drag from Column A-F Headers (Highlighted Columns) to select your table array.
Type a comma after selecting your table array.
Step 25: Second Example With VLOOKUP
Type “2” for the next parameter because we want to return the second column information.
Type one more comma and then type “FALSE”.
End your parentheses and hit enter.
Step 26: Second Example With VLOOKUP
Copy cell G2. Highlight the remaining cells below G2. Paste the formula in the cells below.
Step 27: Second Example With VLOOKUP
The data should now look like the following:
Step 28: Second Example With VLOOKUP Video Instructions
This video walks through the process of executing the formula for the second example.