Excel Lesson 6:
The VLookup Function
The VLookup Function
The VLookup Function is similar to the Choose Function, but it allows you to display data based on a range of numbers instead of just a single number.
- 1. Create your spreadsheet like the one below:
We will use the VLookup Function to display the shipping and handling costs based on the weight. In order to use the VLookup Function we need to create a table array that contains the ranges of numbers that will determine the cost.
- 2. Click in Cell A8
- 3. Create the table array below
| Weight | Shipping |
|---|---|
| 0 - 10 | 6.49 |
| 21 - 30 | 8.73 |
| 31 - 75 | 12.56 |
| 76 - Over | 25.00 |
- 4. Convert the shipping costs to Currency Style
- 5. Click in Cell C3
- 6. Type "=VLOOKUP(B3, $A$9:$B$12, 2)"
The VLookup Function lookup value is the cell in your main table that determines the value the VLookup Function will output. The second parameter of the function is the location of the table array. You can type this in or click-and-drag to add it to the function. Notice that the cells are locked so we can easily copy this formula. The table array will always be located in the same place so we need to lock its cell reference. Finally, the column index number indicates how many columns are in the table array. In this course we will always use 2-column table arrays so you can just remember to always use 2 for this parameter.
After entering the formula you may have noticed that Excel displays #N/A in the cell. This is because there is an error in our table array. The VLookup Function can determine the ranges on its own and we only need to provide the lowest number for each range.
- 7. Change the table array so it contains the lowest number in each range
- 8. Use the Fill Handle to drag the formula in Cell C3 to Cell C6
- 9. Create a new table array based on the information below
| Weight | Handling |
|---|---|
| 0 - 5.5 | 3 |
| 5.6 - 20 | 12 |
| 21 - 50 | 48 |
| 51 - Over | 154 |
- 10. Convert the handling costs to Currency Style
- 11. Use the VLookup Function to determine the handling costs
Remember that the table array should not contain ranges of numbers since the VLookup Function will determine the ranges on its own. Your spreadsheet should look like the one below when you are done.
Sorting Data
A commom task in Excel is sorting data. Excel makes this pretty easy to do, and you can sort by almost any type of data.
- 12. Select Cell A2 through Cell D6
- 13. Go to Home>Editing>Sort & Filter>Custom Sort...
It is important to select all of the data in your table. If you just select the items to sort the rest of the data will not move. Luckily, Excel 2007 gives you a warning if you try to do this. Once the Custom Sort window is open you can choose what field you want to sort by. Since we selected the column titles they appear in the Sort By section. If we hadn't selected the titles it would simply say "Column A" instead of "Customer." You can also choose whether to sort in ascending or descending order.
- 14. Sort the table by "Shipment Weight" in Ascending Order