WEEK 3 Continued: VLOOKUP
There are a few ways to do a vlookup in excel. One is in the same spreadsheet, another is the same excel file but a different spreadsheet, and the other is a completely different excel file.
What a Vlookup does it is looks for a value and returns another value relevant to this value. Confused?
Let's practice so you can see what I mean.
Firstly, let's begin by returning a value in the same spreadsheet.
1. Open the following file:
What a Vlookup does it is looks for a value and returns another value relevant to this value. Confused?
Let's practice so you can see what I mean.
Firstly, let's begin by returning a value in the same spreadsheet.
1. Open the following file:
vlookup_example1.xls |
Here we see the first name of a Sales person and their corresponding amount of customer, sales and profit and loss.
Beside our table are questions and their answers.
Beside our table are questions and their answers.
Using the vlookup formula we will display the same answers.
2. Make sure your cursor is in cell H2. Then click on the fx next to the formula bar.
2. Make sure your cursor is in cell H2. Then click on the fx next to the formula bar.
3. Type in vlookup in the search area, and click Go.
Then click ok.
4. You will see a function argument pop up for the vlookup function:
4. You will see a function argument pop up for the vlookup function:
5. The first question is: How many sales did John make?
- Therefore we want to have our Lookup_value to be John.
- The Table_array searches all the possible columns and rows for any data we may need to search for and return.
- Col_index_num, is the number of the column of the value you are returning - in this case, Column 3 (Net Sales)
- And the Range_lookup is False.
6. Click ok and see the value displayed in cell H2.
Exercise 9:
1. Using the vlookup formula. Answer Questions 2, 3, 4 in the rest of the excel file.
VLOOKUP IN DIFFERENT SPREADSHEETS
1. Open the following file:
vlookup_example2.xls |
2. Looking at the bottom of the file at the Tabs, you will notice that there are 2 different spreadsheets, in the same file. One called Voters and the other called Party Codes.
In the Voter's spreadsheet you will see the following:
In column D we will enter our vlookup formula to search the Pcode (PartyCode) in our Party Codes speadsheet and returned the corresponding name of the Political Party.
Click on the Party Codes tab and manually search for Pcode A. The value that you find should be Democratic.
In this case rather than searching manually and entering in the value manually - using a vlookup table saves SO much time!
3. Click on cell D2 and click on the fx button near the formula bar
Click on the Party Codes tab and manually search for Pcode A. The value that you find should be Democratic.
In this case rather than searching manually and entering in the value manually - using a vlookup table saves SO much time!
3. Click on cell D2 and click on the fx button near the formula bar
4. Select the vlookup function like in the last tutorial.
5. This time rather than typing in a specific value, click on the cell that holds the value you want to search for.
5. This time rather than typing in a specific value, click on the cell that holds the value you want to search for.
6. In the Table_array field - Click on the Party Codes tab and highlight all the fields in our table.
7. Click in the Col_index_num field and enter in the number of the field your value is in that you are returning. In this case, it’s the value in the Party Code spreadsheet that has the name of the Political Party. Enter a 2 in this field.
8. And finally enter in False for the Range_lookup9. Practice by looking for the other names for each value. In the spreadsheet.
9. Practice by looking for the other names for each value. In the spreadsheet.
10. Change the Values in Pcode and see the Party Name automatically update!
9. Practice by looking for the other names for each value. In the spreadsheet.
10. Change the Values in Pcode and see the Party Name automatically update!
Exercise Extension:
1. Open the following file:
extension_vlookup.xlsx |
2. The exercises that you need to complete are in the files.