PLEASE USE THE ATTACHED DATA. PART $1$ AND PART $2$ SHOULD BE COMPLETED SEPARATELY. PLEASE SHOW THE EXCEL FORMULA USED. THANKS.

Students should be able to:

$$ Use cell referencing in calculations $($mixed and absolute references$)$

$$ Use Data Validation to restrict data type

$$ Use XLOOKUP

$$ Use IF function and nested if statements

$$ Use conditional formatting

Context

A local grocery store has been receiving complaints from customers that products on their shelves are expired. You$$ve been hired as their engineering intern to create an Excel worksheet which will help employees know if products on their shelves should be removed.

Task

Part $1$: Use the Excel Template worksheet entitled $$Grocery Store Part $1$ to complete the following:

$1.$ Use data validation to select a date of interest from the date range in column R$.$

$2.$ Use data validation to select a product of interest from the products in column L$.$

$3.$ Populate C$2-$F$2$ using XLOOKUP and mixed$/$absolute referencing.

$4.$ In G$2,$ use an IF statement to determine if the product is expired $($as compared to the date of interest selected in cell A$2).$

a$.$ If it$$s expired, display the status as EXPIRED in cell G$2.$

b$.$ If it$$s still good, display the status as OK in cell G$2.$

$5.$ Use conditional formatting to color$-$code the status in G$2.$

a$.$ If it$$s expired, display the status in dark red text with a light red background.

b$.$ If it$$s still good, display the status in dark green text with a light green background

PART $2$: Use the Excel Template worksheet entitled $$Grocery Store Part $2$ to complete the following:

$1.$ Copy over your table $($cells A$1$:G$2)$ from part $1.$

$2.$ Update your data validation to select a product of interest from the extended list in column L$.$

$3.$ Adjust cells C$2-$F$2$ to ensure your XLOOKUP is referencing the extended table $($down to row $50).$

$4.$ In G$2,$ update to a nested IF statement to determine if the product is expired $($as compared to the date of interest selected in cell A$2).$

a$.$ If the product doesn$$t expire, display the status as NEVER EXPIRES in cell G$2.$

b$.$ If it$$s expired, display the status as EXPIRED in cell G$2.$

c$.$ If it$$s still good, display the status as OK in cell G$2.$

$5.$ Update your conditional formatting to color$-$code the status in G$2.$

a$.$ If it never expires, display the status in dark yellow text with a light yellow background.

b$.$ If it$$s expired, display the status in dark red text with a light red background.

c$.$ If it$$s still good, display the status in dark green text with a light green background