Task 3 (Worksheet 3). 1 question (3 marks)

Determine expense budget: The Staff table includes an EmployeeID, Name and Job Role details. It also has empty cells for an Expense budget for staff for the years 2021, 2022 and 2023 (D4:F11). The Expense budget table includes an Employee ID (Emp_ID) in column H. The Emp_ID corresponds with the last part of the Job role text in column C. For instance, Franks job role ends with the characters (ExOf). The E and the O combine match with the EMP_ID (EO). Write a formula that uses the Expense budget table to populate columns D:F with the correct figure. Do this with one formula that is created for D4 and copied to D4:F11.

You must use VLOOKUP and other functions we have covered in the tutorials Define H2:K11 as a Named Range called Expense_budget and use this in your VLOOKUP

EXAMPLE: Franks Job role is Executive officer (ExOf), which matches with EO in the Expense budget table. Therefore, his 2021 budget is $15,000, his 2022 budget is $20,000, and his 2023 budget is $25,000.

Staff table Expense budget 2021 2022 2023 2023 EmployeeID 123A 123B Emp_ID EO AA 1230 RM Name Frank Kraemer Jack Cersi Ken Ruilin Stellios 123D Expense budget 2021 $ 15,000 $ $ 3,000 $ $ 8,000 $ $ 8,000 $ $ 10,000 $ $ 11,000 $ $ 7,000 $ $ 8,500 $ Job role Exective officer (ExOf) Administration assistant (AdAs) Research manager (ReMa) Head of division (HeDi) Accounts manager (ACMa) Team lead (Tele) Project manageer (PrMa) Finance officer (FiOf) AM 2022 20,000 $ 5,000 $ 10,000 $ 10,000 $ 15,000 $ 13,000 $ 9,000 $ 10,000 $ 25,000.00 7,000.00 120,000.00 120,000.00 20,000.00 15,000.00 11,000.00 11,500.00 123E HD PM 123F 123G 123H TL FO