Please complete the questions as outlined above, complete the yellow and orange section, I need the function not only the answer, eg: what I need is the function like :=IF(AND(E5>10000,F5>10000),VLOOKUP(SUM(E5,F5),Commission_rates,2,TRUE),0)*SUM(E5,F5)

Task 2 Cells Task/problem Example Excel Functions/features to you should use H6-H10 Consultants that Sally Chan started in 2020 and IF, CONCAT, AND, YEAR started in the year generated over $75,000 in sales in (or DATEVALUE) 2020 are entitled both "January-June sales" and "July- to bonus December sales" thus H8 should commission if they display "Sally Chan receives bonus generate over commission". $75,000 in sales in both periods. Hugo Chevi did not start in 2020 thus H7 should display N/A. If an employee started in 2020, Tyrion Smith started in 2020 but did and both periods not generate sales over $75,000 in "January-June sales in both "January-June sales" sales" and "July and "July-December sales" and thus December sales" H9 should display "N/A. are greater than $75,000 then then the cell should display "First name Last Name receives bonus commission", if the conditions are not met the cell should display "N/A". 15-110 Create the email David Silver's email address in cell 15 VLOOKUP, Left, Right, address in 15-110 should display as: CONCAT using the initial D.Silver@bgconsulting.com" from the first Define the name range name, plus the full Sally Chan's email address in cell 18 E25 to F30 as last name. Add a should display as: "Consultant details" and "." between the "S. Chan@formation.com" use this in your initial and last VLOOKUP name. Add the "@" symbol after the last name. Finally use the Consultant details table to obtain the email domain name. 3 H Bonus commission Email address A B C D E F G 1 2 3 4 First Nam Last Nam Date Starte Consultant | January-June sale July-December sal Commission ($) 5 David Silver 2/09/14 XXDASIL $ 40,000.00 $ 90,000.00 $ 7,800.00 6 Harriette Brown 11/08/15 XXHABRO $ 100.000.00 $ 90,000.00 $ 15,200.00 7 Hugo Chevi 11/11/19/XXHUCHE $ 8,000.00 $ 33,000.00 $ 8 Sally Chan 14/06/20|XXSACHA $ 85,000.00 $ 76,000.00 $ 12,880.00 9 Tyrion Smith 1/04/20 XXTYSMI $ 60,000.00 $ 95,000.00 12,400.00 10 Jamie Brown 1/07/17 XXJABRO $ 90,000.00 $ 110,000.00 $ 20,000.00 11 12 13 14 Commission rates table 15 Sales threshold Commission 16 $ 10,000.00 2% 17 $ 51,000.00 4% 18 $ 101,000.00 6% 19 $ 150,000.00 8% 20 $ 200,000.00 10% 21 22 23 Consultant details table 24 Consultant ID Email domain 25 DASIL bgconsulting.com 26 HABRO flywizards.com 27 HUCHE acesolutions.com 28 SACHA formation.com 29 TYSMI smothers.com 30 JABRO tbit.com Task 2 Cells Task/problem Example Excel Functions/features to you should use H6-H10 Consultants that Sally Chan started in 2020 and IF, CONCAT, AND, YEAR started in the year generated over $75,000 in sales in (or DATEVALUE) 2020 are entitled both "January-June sales" and "July- to bonus December sales" thus H8 should commission if they display "Sally Chan receives bonus generate over commission". $75,000 in sales in both periods. Hugo Chevi did not start in 2020 thus H7 should display N/A. If an employee started in 2020, Tyrion Smith started in 2020 but did and both periods not generate sales over $75,000 in "January-June sales in both "January-June sales" sales" and "July and "July-December sales" and thus December sales" H9 should display "N/A. are greater than $75,000 then then the cell should display "First name Last Name receives bonus commission", if the conditions are not met the cell should display "N/A". 15-110 Create the email David Silver's email address in cell 15 VLOOKUP, Left, Right, address in 15-110 should display as: CONCAT using the initial D.Silver@bgconsulting.com" from the first Define the name range name, plus the full Sally Chan's email address in cell 18 E25 to F30 as last name. Add a should display as: "Consultant details" and "." between the "S. Chan@formation.com" use this in your initial and last VLOOKUP name. Add the "@" symbol after the last name. Finally use the Consultant details table to obtain the email domain name. 3 H Bonus commission Email address A B C D E F G 1 2 3 4 First Nam Last Nam Date Starte Consultant | January-June sale July-December sal Commission ($) 5 David Silver 2/09/14 XXDASIL $ 40,000.00 $ 90,000.00 $ 7,800.00 6 Harriette Brown 11/08/15 XXHABRO $ 100.000.00 $ 90,000.00 $ 15,200.00 7 Hugo Chevi 11/11/19/XXHUCHE $ 8,000.00 $ 33,000.00 $ 8 Sally Chan 14/06/20|XXSACHA $ 85,000.00 $ 76,000.00 $ 12,880.00 9 Tyrion Smith 1/04/20 XXTYSMI $ 60,000.00 $ 95,000.00 12,400.00 10 Jamie Brown 1/07/17 XXJABRO $ 90,000.00 $ 110,000.00 $ 20,000.00 11 12 13 14 Commission rates table 15 Sales threshold Commission 16 $ 10,000.00 2% 17 $ 51,000.00 4% 18 $ 101,000.00 6% 19 $ 150,000.00 8% 20 $ 200,000.00 10% 21 22 23 Consultant details table 24 Consultant ID Email domain 25 DASIL bgconsulting.com 26 HABRO flywizards.com 27 HUCHE acesolutions.com 28 SACHA formation.com 29 TYSMI smothers.com 30 JABRO tbit.com