The VLOOKUP Function

The VLOOKUP function is typically used to access and display data located in another worksheet or workbook. The function can also be used to access and display data located in the same worksheet. This is a very powerful and versatile function because it eliminates the need to copy or recreate data that exists in other worksheets or workbooks. It is called a VLOOKUP function because the function will search vertically down the first column of a range of cells to find what is called a lookup value. This process is very similar to the statistical IF functions in Section 3.2 "Statistical IF Functions". You will recall that these functions used criteria to select cells from a range that was used in the mathematical output. The VLOOKUP function is essentially performing the same process; however, instead of selecting multiple cells from a range, the function is only looking for one specific cell location. Once the function finds the specific cell location, it will display the contents of that cell location or another cell location in the range. Before using the VLOOKUP function in the personal investment portfolio workbook, it is strongly recommended that you carefully read the definitions for the function arguments listed in Table 3.10 "Arguments for the VLOOKUP Function".

You may have noticed that on the Investment Detail worksheet, the Description column is blank (see Figure 3.2 "Investment Detail Worksheet"). Descriptions for several investments are included in the workbook in the Investment List worksheet as shown in Figure 3.42 "Investment List Worksheet". The VLOOKUP function will be used to search for a specific symbol in Column A of the Investment List worksheet and display the description for that symbol located in Column B. The following steps explain how to accomplish this:

Figure 3.42 Investment List Worksheet

1. Click cell C4 on the Investment Detail worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Lookup & Reference button in the Function Library group of commands.
4. Select the VLOOKUP function from the list of functions. Use the scroll bar to scroll down to the bottom of the list. This will open the Function Arguments dialog box for the VLOOKUP function.
5. Click the Collapse Dialog button next to the Lookup_value argument on the Function Arguments dialog box.
6. Click cell B4 and press the ENTER key on your keyboard. The symbol in cell B4 is the lookup value that will be searched in the first column of the range defined for the Table_array argument.
7. Click the Collapse Dialog button next to the Table_array argument on the Function Arguments dialog box.
8. Click the Investment List worksheet tab.
9. Highlight the range A3:F23 on the Investment List worksheet and press the ENTER key on your keyboard. The function will look in Column A of this range for the lookup value.
10. Click in the input box for the Table_array argument and place an absolute reference on the range A3:F23. This is done by typing a dollar sign ($) in front of the column letter and row number of each cell location in the range.
11. Press the TAB key on your keyboard to advance to the Col_index_num argument and type the number 2. Once the function finds the lookup value in Column A of the range A3:F23, it will display the description that is in Column B of the same row.
12. Press the TAB key on your keyboard to advance to the Range_lookup argument and type the word FALSE. This will direct the function to search for only exact matches to lookup value.
13. Click the OK button at the bottom of the Function Arguments dialog box.
14. Copy the VLOOKUP function in cell C4 and paste it into the range C5:C18 using the Paste Formulas command.

Figure 3.43 "Completed Function Arguments Dialog Box for the VLOOKUP Function" shows the completed Function Arguments dialog box for the VLOOKUP function. Notice that the Range_lookup argument is defined with the word FALSE. This will direct the function to search for an exact match to the lookup value and will also direct the function to search the entire first column of the table array range. Finally, it is important to note the absolute reference on the table array range. This will prevent the table array range from changing when the function is pasted into other cell locations.

Figure 3.43 Completed Function Arguments Dialog Box for the VLOOKUP Function

Figure 3.44 "Results of the VLOOKUP Function in the Investment Detail Worksheet" shows the results of the VLOOKUP function in the Investment Detail worksheet. The function is searching for each symbol in Column B of the Investment Detail worksheet in Column A of the Investment List worksheet. When the function finds a match, it will display whatever is in the cell location two columns to the right, or Column B, in the Investment List worksheet. For example, the symbol VDMIX, which is in cell B8 on the Investment Detail worksheet (see Figure 3.44 "Results of the VLOOKUP Function in the Investment Detail Worksheet"), is also in cell A15 on the Investment List worksheet (see Figure 3.42 "Investment List Worksheet"). As a result, the function is displaying whatever is in cell B15 on the Investment List worksheet, which is the description “Developed Markets.”

Figure 3.44 Results of the VLOOKUP Function in the Investment Detail Worksheet

The HLOOKUP Function

The HLOOKUP function serves the same purpose as the VLOOKUP function. The HLOOKUP function can be used to display data from another worksheet or workbook. However, instead of searching for the lookup value vertically down the first column of the table array range, the HLOOKUP function searches horizontally across the first row of the table array range. When the function finds a match for the lookup value, it will display the contents in a cell location based on a row index number. This number designates how many rows below the first row of the table array range the function should display. Table 3.11 "Arguments for the HLOOKUP Function" provides a definition for each argument of the HLOOKUP function. It is best to review the definitions of these arguments carefully before using the function.

The HLOOKUP function will be used on the Portfolio Summary worksheet to display the benchmark growth rates in the range G4:G7. A benchmarkA value or qualitative example that can be used as a standard point of comparison. Results that are higher or better than a benchmark are typically considered positive, whereas results that are lower or worse than the benchmark are typically considered negative. is a value that can be used as a standard point of comparison. The Benchmarks worksheet contains growth rates at different year intervals for the benchmarks that will be used to compare the performance for each investment type (see Figure 3.45 "Benchmarks Worksheet"). For the purposes of this workbook, we will be comparing the growth rates for each investment type to the 5-year average growth rate for the benchmarks categories listed in the range H4:H7. The following steps explain how to construct the HLOOKUP function to display the 5-year benchmark values in the Portfolio Summary worksheet:

Figure 3.45 Benchmarks Worksheet

1. Click cell G4 in the Portfolio Summary worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Lookup & Reference button in the Function Library group of commands.
4. Select the HLOOKUP function from the list of functions. This will open the Function Arguments dialog box for the HLOOKUP function.
5. Click the Collapse Dialog button next to the Lookup_value argument on the Function Arguments dialog box.
6. Click cell H4 and press the ENTER key on your keyboard. The description in cell H4 will be the lookup value that will be searched in the first row of the range defined for the Table_array argument.
7. Click the Collapse Dialog button next to the Table_array argument on the Function Arguments dialog box.
8. Click the Benchmarks worksheet tab.
9. Highlight the range B2:E6 on the Benchmarks worksheet and press the ENTER key on your keyboard. The function will look in Row 2 of this range for the lookup value.
10. Click in the input box for the Table_array argument and place an absolute reference on the range B2:E6. This is done by typing a dollar sign ($) in front of the column letter and row number of each cell location in the range.
11. Press the TAB key on your keyboard to advance to the Row_index_num argument and type the number 4. Once the function finds the lookup value in Row 2 of the range B2:E6, it will display the value that is in Row 5 of the same column. Remember that Row 2 is counted as Row 1 for the row index number in this example.
12. Press the TAB key on your keyboard to advance to the Range_lookup argument on the Function Arguments dialog box and type the word FALSE. This will direct the function to search for only exact matches of the lookup value.
13. Click the OK button at the bottom of the Function Arguments dialog box.
14. Copy the HLOOKUP function in cell G4 and paste it into the range G5:G7 using the Paste Formulas command.

Figure 3.46 "Completed Function Arguments Dialog Box for the HLOOKUP Function" shows the completed Function Arguments dialog box for the HLOOKUP function. The row index number 4 indicates that the function will display the contents of the cell location in the fourth row of the table array range.

Figure 3.46 Completed Function Arguments Dialog Box for the HLOOKUP Function

Figure 3.47 "Completed Portfolio Summary Worksheet" shows the output of the HLOOKUP function. Notice that the output of the function in cell G4 is 6.0%. This is because the lookup value was defined with the entry in cell H4, which is the Barclays index. Looking at Figure 3.45 "Benchmarks Worksheet", if you count the first row of the table array range as Row 1, the value 6.03% is the fourth row in the Barclays column. Since the values in Column G on the Portfolio Summary worksheet are set to 1 decimal place, the value is displayed as 6.0%.

Figure 3.47 Completed Portfolio Summary Worksheet

Web Queries

The final skill we will demonstrate in this chapter is the creation of a web query. Web queries allow you to import external data from a website into an Excel worksheet. We can enhance the personal investment portfolio demonstrated in this chapter through the use of web queries to import current stock prices from a website. Once a stock price is imported into the workbook, a cell reference can be added to the Investment Detail worksheet to reflect the most current price for an investment. It is important to note that you must establish an Internet connection before proceeding with this exercise. The following steps explain how to retrieve the current stock price for Microsoft from the Yahoo! Finance website and import it into the Price Data worksheet:

1. Click cell A2 on the Price Data worksheet.
2. Click the Data tab of the Ribbon.
3. Click the From Web button located in the Get External Data group of commands. This will open the New Web Query dialog box.
4. Type the following Internet address (URL) in the Address input box (see Figure 3.48 "New Web Query Dialog Box"): http://www.yahoo.com. Press the ENTER key on your keyboard after entering the URL.

5. Use the scroll bar on the right side of the New Web Query dialog box to locate the Finance link on the left navigation pane of the website. Click the Finance link (see Figure 3.48 "New Web Query Dialog Box").

   Figure 3.48 New Web Query Dialog Box

   
6. Locate the Get Quotes input box by using the scroll bar to scroll to the top of the website (see Figure 3.49 "Get Quotes Input Box on Yahoo! Finance").

7. Click in the Get Quotes input box, type the stock symbol MSFT, and press the ENTER key on your keyboard (see Figure 3.49 "Get Quotes Input Box on Yahoo! Finance").

   Figure 3.49 Get Quotes Input Box on Yahoo! Finance

   

8. When viewing the stock price data for MSFT, you will see several yellow boxes or tags with arrows (see Figure 3.50 "Selecting a Tag to Import Data from a Website"). These tags indicate blocks or tables of data that can be imported into a worksheet from a website. Click the yellow tag next to the Last Trade price. The yellow tag will turn green with a check mark inside of it.

   Figure 3.50 Selecting a Tag to Import Data from a Website

   

9. Click the Import button at the bottom of the New Web Query dialog box. This will open the Import Data dialog box, as shown in Figure 3.51 "Import Data Dialog Box".

   Figure 3.51 Import Data Dialog Box

   
10. Click the Properties button at the bottom of the Import Data dialog box (see Figure 3.51 "Import Data Dialog Box"). This will open the External Data Range Properties dialog box.
11. Click in the Name input box on the External Data Range Properties dialog box and delete the query name that is provided. Type the new query name Microsoft Stock Price.

12. Click the box next to the “Refresh every” property. Change the refresh minutes to 5. This means the query will automatically search the website every 5 minutes to retrieve an updated stock price for Microsoft (see Figure 3.52 "Final Settings for the External Data Range Properties Dialog Box").

    Figure 3.52 Final Settings for the External Data Range Properties Dialog Box

    
13. Click the option at the bottom of the External Data Range Properties dialog box that reads “Overwrite existing cells with new data, clear unused cells” (see Figure 3.52 "Final Settings for the External Data Range Properties Dialog Box"). It is important to select this option if you intend to add more than one web query to a worksheet or if you are using formulas in adjacent columns to a web query. This option will prevent the web query from deleting entire rows of data from the worksheet.
14. Click the OK button at the bottom of the External Data Range Properties dialog box.
15. Click the OK button at the bottom of the Import Data dialog box.
16. After the data is imported, click cell H17 on the Investment Detail worksheet and type an equal sign (=).
17. Click the Price Data worksheet tab.
18. Click cell B2 on the Price Data worksheet and press the ENTER key on your keyboard. This will display the price that is imported from the web query on the Investment Detail worksheet.
19. Figure 3.53 "Stock Price for Microsoft Imported into the Price Data Worksheet" shows the results of importing the Microsoft stock price from the Yahoo! Finance website. It is important to note that the data you retrieve from the website will be different from what is shown in this figure.

Figure 3.53 Stock Price for Microsoft Imported into the Price Data Worksheet