Creating a copy of a worksheet -Sorting the contents of a worksheet -Using VLOOKUP in approximate…

Creating a copy of a worksheet -Sorting the contents of a worksheet -Using VLOOKUP in approximate…

In order to complete this homework, you must open the Homework #2 Excel workbook. This file is a template that contains a few sheets: “Products – Master”, “Employees – Master”, “VLOOKUP Template”, and “INDEX_MATCH Template”. You will use each of these sheets throughout the course of this assignment. Topics covered in this homework assignment include the following: • Creating a copy of a worksheet • Sorting the contents of a worksheet • Using VLOOKUP in approximate match mode against an external table/range • Using VLOOKUP in exact match mode against an external table/range • Using MATCH to return an item’s position in a range • Using INDEX (and MATCH) to find a corresponding attribute of a given item against an entire external table/range • Using INDEX (and MATCH) to find a simple result using only one column of an external table/range Your directions begin on the next page. Good luck!
Problem #1 1. Create a copy of the “Products – Master Sheet” and rename the copy “Products – Problem 1”. 2. Sort your “Products – Problem 1” sheet alphabetically by the ‘Product Name’ column 3. Create a copy of the “VLOOKUP Template” sheet and rename the copy “Problem 1” 4. Using the new “Problem 1” sheet, fill in the “Item to find” with “Pears” (which is a ‘Product Name’) 5. Using the new “Problem 1” sheet, replace the result column text called “Column 1” with “Standard Cost” 6. Using the new “Problem 1” sheet, replace the result column text called “Column 2” with “List Price” Now that you’ve copied your sheets and renamed your fields you can proceed with creating VLOOKUPS on your “Problem 1” sheet 7. Use a VLOOKUP to find the “Standard Cost” for “Pears” (#4 and #5 above) on the “Products – Problem 1” sheet. You may use either an approximate or exact VLOOKUP, so long as the result is correct. If the item is not found, a value of “#N/A” is appropriate 8. Use a VLOOKUP to find the “List Price” for “Pears” (#4 and #6 above) on the “Products – Problem 1” sheet. You may use either an approximate or exact VLOOKUP, so long as the result is correct. If the item is not found, a value of “#N/A” is appropriate

Looking for a similar assignment? Get help from our qualified experts!

Order Now

Related Posts