1. VLOOKUP
Welcome to the final lesson of the course!
2. The power of VLOOKUP()
This lesson will focus solely on what many call the most powerful function in spreadsheets: VLOOKUP.
VLOOKUP, or vertical lookup, is used to search for values in a table based upon a key to search for. It is commonly used to add information to a table that is present in another table. In this case, we have two tables: one containing order information, and another containing product information. VLOOKUP could be used to add the unit price information to the order table based on the product number keys present in both tables.
3. The power of VLOOKUP()
The table we are adding information to is often called the main table, and the table containing the desired information is often referred to as the lookup table. We'll use these terms going forward.
It is best practice to keep information relating to different entities in different tables, in this case, orders and products. This reduces the amount of redundant information being stored.
This makes VLOOKUP the best friend of many analysts out there!
4. VLOOKUP() syntax
The VLOOKUP function takes four arguments. The search_key is the value in the main table that we should use to search for in the lookup table. The range is the cell range containing the data in the lookup table. We usually use absolute references when specifying the range argument, so it remains the same if the formula is copied into another cell.
The index argument is a number used to tell the function which column in the lookup table should be returned, and finally, is_sorted is an optional argument that takes TRUE or FALSE depending on whether the lookup table is sorted.
This is a lot of information to take in, but it'll all come together in an example.
5. VLOOKUP() example - stationary orders
Let's return the to stationary orders data we already saw. We want to calculate the total price of each order by looking up the unit price information from the products table, and then multiplying the quantity and unit price. This is a perfect occasion to use VLOOKUP!
6. VLOOKUP() example - stationary orders
The first argument to VLOOKUP is the key in the main table to search for in the lookup table, so we reference the product number for that row. We'll copy the formula downwards to populate each unit price, but for now, we'll just focus on the first row.
7. VLOOKUP() example - stationary orders
The next argument to VLOOKUP is the range containing the lookup table data, which is the range A11 to C14. We don't include the headers of the lookup table when we specify this range, and we use absolute references, so the range doesn't shift when we copy the formula. Recall that absolute references can be specified by placing dollar-signs before the row and column indexes.
8. VLOOKUP() example - stationary orders
The index argument is the index of the column in the lookup table containing the values we want to return. We want the unit price values, which is the third column in the lookup table.
9. VLOOKUP() example - stationary orders
Finally, because the lookup table isn't sorted, we specify is_sorted as FALSE.
10. VLOOKUP() example - stationary orders
This returns six dollars ninety-five for the first order.
11. VLOOKUP() example - stationary orders
Because we used absolute references to specify the lookup table range, we can copy the lookup formula downward to populate the rest of the unit prices.
12. VLOOKUP() example - stationary orders
Finally, to calculate the total price, we multiply the quantity and unit price,
13. VLOOKUP() example - stationary orders
which we can again copy downwards to complete the table.
14. Let's practice!
Over to you! Time to get familiar with the most powerful function in spreadsheets.