03 Assignment – VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP

Continue working with the Employee Contact Information workbook you created last week for your boss. Now she needs a way to retrieve an employee’s name by typing in an employee number.

For example, if the employee’s name is ‘Sandy’ and her employee number is 1234, your boss wants to be able to find ‘Sandy’ by typing in 1234.

Use the VLOOKUP or HLOOKUP functions to search the values in the columns or rows to retrieve the needed data. Pick an employee of your choice to demonstrate the lookup function. Follow these steps:

1. In cell A8, type in one of the employee ID numbers that you entered into your contact list.
2. Click in cell B8 and click the Insert Function button.

   

3. Click the VLOOKUP function (NOTE: if you do not have it, type “vlookup” into the search box and click Go).
4. Fill out the three arguments as described in the text:
   1. The lookup_value is the cell that has your ID number (click B8)
   2. The Table_array is the range – click and drag to select the table (note: do not include the column headers)
   3. The Column_index_num is the column in which you will find the Employee Name (type in the number 2)
5. Click the OK button.
6. Test by entering a different number in cell B8.

Submit your completed assignment by following the directions linked below. Please check the Course Calendar for specific due dates.

Save your assignment as a Microsoft Excel workbook. (Mac users, please remember to append the “.xlsx” extension to the filename.) The name of the file should be your first initial and last name, followed by an underscore and the name of the assignment, and an underscore and the date.

03 Course Project – Analyzing with Functions

Analyzing with Functions

Functions are predefined formulas already available within Excel. By using them within a portfolio, Excel will do the needed financial calculations. Include the following functions within your project workbook.

1. Use the SUM function to add the cost paid for each stock in your portfolio.
2. Find the AVERAGE, MINIMUM, and MAXIMUM (using the appropriate functions) of:
   • number of stocks purchased
   • purchase price of each stock
   • price paid for all stocks in your portfolio
3. Perform a simple conditional analysis by using at least one logical function (i.e., IF, AND, OR…). Apply the test on only cells that contain data. For example, show whether the purchase price of one stock is higher or lower than the average purchase price of all stocks in your portfolio. In the column header to the right of your logical function, briefly explain the test being performed.
4. Enter the function labels (SUM, AVERAGE, etc.) in the cells adjacent to the calculations so that a reader can easily tell what calculations are present in the worksheet.

Here is a sample screen shot of the finished assignment:

Submit your completed assignment by following the directions below. Please check the Course Calendar for specific due dates.