IGH 000018 Regular Student Report 2 Student ID 3 Teacher 4 Average Fee 5 Skill Level 6 Total Lessons 7 Total Paid this Year 8 Bonus Lessons Competiton Fees Registration Type Travel Type Uniform Size Uniform Fee Registration Fee Transportation Fee Total Number Needed Price Per Uniform Size Uniform Size Ready to go! 10 Competition Report 11 Student ID Student Name Permission Slip? 12 000001 13 000002 14 000003 15 000006 16 000008 17 000010 18 000016 19 000018 20 000020 21 000022 Student Data LessonData DataTables Edit DE 3 Lesson Pricing 1.0 2.0 Registration Fee Early Bird Regular 250 $ 500 Late 750 30 60 $ 5 Beginner 6 Intermediate 7 Advanced 8 Expert 35 45 35 40 50 40 $ 10 Transportation Fees per Person Flight 30 $ 277 382 10 426 0 592 Bus 30 82 84 86 Charter $ 10 115 120 125 Drive $ 190 190 190 190 17 Uniform Fees 0 $ 42.23 13 43.21 $ $ $ 4710 45.12 4153 46.15 4.51 45.49 44.19 42.55 4 0.97 40.13 19.99 39.15 41.95 41.1142.09 E F G H Total Fee A B C D 1 Lesson Data 2 Date StudentID Student Teacher 3 1/7/2018 000018 Kennan Meyer Jenny 4 1/8/2018 000001 Zia White Chuck 5 1/12/2018 000033 Belle Solomon Chuck 6 1/16/2018 000010 Hyatt Waters Chuck 7 1/17/2018 000018 Kennan Meyer Anne 8 1/19/2018 000028 Amena Sanchez Chuck 9 1/21/2018 000022 Rina Stanley Debbie 10 2/3/2018 000008 Violet Ward Debbie 11 2/14/2018 000008 Violet Ward Chuck 12 2/16/2018 000001 Zia White Mark 13 2/20/2018 000016 Pamela Lambert Chuck 14 3/10/2018 000002 Pandora Brock Mark 15 4/2/2018 000006 Summer Sweeney Debbie 16 4/15/2018|000022 Rina Stanley Jenny 17 4/16/2018 000006 Summer Sweeney Chuck 18 4/19/2018 000018 Kennan Meyer Chuck 19 4/20/2018|000033 Belle Solomon Chuck 20 5/6/2018 000008 Violet Ward Anne 21 5/7/2018|000018 Kennan Meyer Anne 22 5/7/2018 000025 Lee Hayden Mark 23 5/8/2018 000008 Violet Ward Debbie 24 5/9/2018 000028 Amena Sanchez Chuck 25 5/12/2018 000016 Pamela Lambert Anne 26 5/15/2018 000016 Pamela Lambert Mark 27 5/17/2018 000001 Zia White Anne StudentDataLessonData Data Tables Lesson Length skill Level 0.50 Expert 1.50 Advanced 0.50 Beginner 1.50 2.00 Expert 1.00 Advanced 1.00 Intermediate 1.00 Advanced 0.50 Advanced 1.00 Advanced 1.00 Beginner 2.00 Intermediate 1.50 Beginner 1 .00 Intermediate 2.00 Beginner 2.00 Expert 1.50 Beginner 1.00 Advanced 2.00 Expert 0.50 Advanced 2.00 Advanced 2.00 Advanced 1.00 Beginner 1.50 Beginner 2.00 Advanced Report Uniform Size Price Per Hour Competition? Yes Yes Yes Yes Yes Yes Yes Yes Yes Permission Slip Skill level Advanced Intermediate Yes Intermediate Beginner Advanced Yes Beginner Yes Expert Yes Ichuck 1 Student Data 2 Studenti StudentName Instrument Teacher 3 000001 Zia White Piano Jenny 4 000002 Pandora Brock Drums Chuck 5000003 Inez Clements Viola Ichuck 6 000006 Summer Sweeney Drums Anne 7 000008 Violet Ward Guitar Chuck 8 000010 Hyatt Waters Drums Jenny 9 000016 Pamela Lambert Cello Jenny 10 000018 Kennan Meyer Piano 11 000020 Anastasia Marshall Cello Jenny 12000022 Rina Stanley Guitar Chuck 13 000025 Lee Hayden Violin Chuck 14 000028 Amena Sanchez Guitar Anne 15 000033 Belle Solomon Trumbone Chuck 16 000014 Mari Sweet Violin 17 000019 Grace Reeves Oboe Chuck 16 000053 Germaine Memilla Flute IChuck 19000057 Merrill Hull Clarinet 20000059 Rinah Parks Tuba Jenny 21 oooooo Tusandra Lana Iviolin Jenny 22 000061 Norman Grimes cello 23 000062 Nicholas Sloan Tuba Jenny 24 000067 Carlos Rose Tuba Jenny 25 000068 Daquan Cabrera Drums Jenny 26 000072 Lani Golden Flute Anne 27 000074 Evangeline Gaines Clarinet Anne Student Data LessonDataDataTables - intermediate Advanced Advanced Beginner Beginner Expert Intermediate Expert Advanced Advanced Advanced Beginner Intermediate Yes Advanced Report EwHelp Search contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing The cost of a lesson varies based on the length of the lesson and the skill level of the student In cell G3, use INDEX and MATCH functions to retrieve the lesson fee for each student from the Lesson Pricing named range. For the students with no skill level, a #N/A error will be retumed. Incorporate an IFERROR function so that if an error is returned, the student is charged $50 for the lesson. Copy the formula down through G29. Hint: Use 0 - Exact Match for the match_type arguments for both MATCH functions. Be sure to use absolute cell references for your lookup_array arguments. The Report worksheet is in need of some work for it to be fully functional. When a Studentin is entered into cell B2, the cells in B3 B8 should retrieve values about the student or calculate values pertaining to the student. In cell B3, enter a VLOOKUP function that will retrieve the teacher's name from the Student_Data named range for the StudentID entered in cell B2. Incorporate an IFERROR function to return a blank value (***) if there is no Studentin value in cell B2 Hint: The the teacher's name to return is in column 4 of the Student Data named range. Also be sure to use a False - Exact Match for the the range_lookup argument. In cell B5, enter a VLOOKUP function that will retrieve the skill level from the Student Data named range for the Students entered in cell B2111 Incorporate an IFERROR function to return a blank value (***) if there is no Studentin value in cell B2 Hint. The skill level value to return is in column 8 of the Student Data named range. Also be sure to use a False . Exact Match for the the range_lookup argument IGH 000018 Regular Student Report 2 Student ID 3 Teacher 4 Average Fee 5 Skill Level 6 Total Lessons 7 Total Paid this Year 8 Bonus Lessons Competiton Fees Registration Type Travel Type Uniform Size Uniform Fee Registration Fee Transportation Fee Total Number Needed Price Per Uniform Size Uniform Size Ready to go! 10 Competition Report 11 Student ID Student Name Permission Slip? 12 000001 13 000002 14 000003 15 000006 16 000008 17 000010 18 000016 19 000018 20 000020 21 000022 Student Data LessonData DataTables Edit DE 3 Lesson Pricing 1.0 2.0 Registration Fee Early Bird Regular 250 $ 500 Late 750 30 60 $ 5 Beginner 6 Intermediate 7 Advanced 8 Expert 35 45 35 40 50 40 $ 10 Transportation Fees per Person Flight 30 $ 277 382 10 426 0 592 Bus 30 82 84 86 Charter $ 10 115 120 125 Drive $ 190 190 190 190 17 Uniform Fees 0 $ 42.23 13 43.21 $ $ $ 4710 45.12 4153 46.15 4.51 45.49 44.19 42.55 4 0.97 40.13 19.99 39.15 41.95 41.1142.09 E F G H Total Fee A B C D 1 Lesson Data 2 Date StudentID Student Teacher 3 1/7/2018 000018 Kennan Meyer Jenny 4 1/8/2018 000001 Zia White Chuck 5 1/12/2018 000033 Belle Solomon Chuck 6 1/16/2018 000010 Hyatt Waters Chuck 7 1/17/2018 000018 Kennan Meyer Anne 8 1/19/2018 000028 Amena Sanchez Chuck 9 1/21/2018 000022 Rina Stanley Debbie 10 2/3/2018 000008 Violet Ward Debbie 11 2/14/2018 000008 Violet Ward Chuck 12 2/16/2018 000001 Zia White Mark 13 2/20/2018 000016 Pamela Lambert Chuck 14 3/10/2018 000002 Pandora Brock Mark 15 4/2/2018 000006 Summer Sweeney Debbie 16 4/15/2018|000022 Rina Stanley Jenny 17 4/16/2018 000006 Summer Sweeney Chuck 18 4/19/2018 000018 Kennan Meyer Chuck 19 4/20/2018|000033 Belle Solomon Chuck 20 5/6/2018 000008 Violet Ward Anne 21 5/7/2018|000018 Kennan Meyer Anne 22 5/7/2018 000025 Lee Hayden Mark 23 5/8/2018 000008 Violet Ward Debbie 24 5/9/2018 000028 Amena Sanchez Chuck 25 5/12/2018 000016 Pamela Lambert Anne 26 5/15/2018 000016 Pamela Lambert Mark 27 5/17/2018 000001 Zia White Anne StudentDataLessonData Data Tables Lesson Length skill Level 0.50 Expert 1.50 Advanced 0.50 Beginner 1.50 2.00 Expert 1.00 Advanced 1.00 Intermediate 1.00 Advanced 0.50 Advanced 1.00 Advanced 1.00 Beginner 2.00 Intermediate 1.50 Beginner 1 .00 Intermediate 2.00 Beginner 2.00 Expert 1.50 Beginner 1.00 Advanced 2.00 Expert 0.50 Advanced 2.00 Advanced 2.00 Advanced 1.00 Beginner 1.50 Beginner 2.00 Advanced Report Uniform Size Price Per Hour Competition? Yes Yes Yes Yes Yes Yes Yes Yes Yes Permission Slip Skill level Advanced Intermediate Yes Intermediate Beginner Advanced Yes Beginner Yes Expert Yes Ichuck 1 Student Data 2 Studenti StudentName Instrument Teacher 3 000001 Zia White Piano Jenny 4 000002 Pandora Brock Drums Chuck 5000003 Inez Clements Viola Ichuck 6 000006 Summer Sweeney Drums Anne 7 000008 Violet Ward Guitar Chuck 8 000010 Hyatt Waters Drums Jenny 9 000016 Pamela Lambert Cello Jenny 10 000018 Kennan Meyer Piano 11 000020 Anastasia Marshall Cello Jenny 12000022 Rina Stanley Guitar Chuck 13 000025 Lee Hayden Violin Chuck 14 000028 Amena Sanchez Guitar Anne 15 000033 Belle Solomon Trumbone Chuck 16 000014 Mari Sweet Violin 17 000019 Grace Reeves Oboe Chuck 16 000053 Germaine Memilla Flute IChuck 19000057 Merrill Hull Clarinet 20000059 Rinah Parks Tuba Jenny 21 oooooo Tusandra Lana Iviolin Jenny 22 000061 Norman Grimes cello 23 000062 Nicholas Sloan Tuba Jenny 24 000067 Carlos Rose Tuba Jenny 25 000068 Daquan Cabrera Drums Jenny 26 000072 Lani Golden Flute Anne 27 000074 Evangeline Gaines Clarinet Anne Student Data LessonDataDataTables - intermediate Advanced Advanced Beginner Beginner Expert Intermediate Expert Advanced Advanced Advanced Beginner Intermediate Yes Advanced Report EwHelp Search contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing The cost of a lesson varies based on the length of the lesson and the skill level of the student In cell G3, use INDEX and MATCH functions to retrieve the lesson fee for each student from the Lesson Pricing named range. For the students with no skill level, a #N/A error will be retumed. Incorporate an IFERROR function so that if an error is returned, the student is charged $50 for the lesson. Copy the formula down through G29. Hint: Use 0 - Exact Match for the match_type arguments for both MATCH functions. Be sure to use absolute cell references for your lookup_array arguments. The Report worksheet is in need of some work for it to be fully functional. When a Studentin is entered into cell B2, the cells in B3 B8 should retrieve values about the student or calculate values pertaining to the student. In cell B3, enter a VLOOKUP function that will retrieve the teacher's name from the Student_Data named range for the StudentID entered in cell B2. Incorporate an IFERROR function to return a blank value (***) if there is no Studentin value in cell B2 Hint: The the teacher's name to return is in column 4 of the Student Data named range. Also be sure to use a False - Exact Match for the the range_lookup argument. In cell B5, enter a VLOOKUP function that will retrieve the skill level from the Student Data named range for the Students entered in cell B2111 Incorporate an IFERROR function to return a blank value (***) if there is no Studentin value in cell B2 Hint. The skill level value to return is in column 8 of the Student Data named range. Also be sure to use a False . Exact Match for the the range_lookup argument