In cell B3, insert a nested MATCH function within an INDEX function that will look up the rental price in column D using the apartment number referenced in cell B2. With 101 entered in cell B2, the lookup function displays $950.00.

Solution :

Step 1:

Open the excel exploring_e07_grader_h1_Apartment.xlsx and save it as exploring_e07_grader_h1_Apartment_LastFirst.

Step 2:

At cell G8 in the Summary worksheet, insert the date function to the number of Years since remodel:

Compute the difference between the years 1/1/2018 value in the cell H2 and the Last remodel date present in cell F8 which returns the number of days.

Then, divide the resultant by 365 days to get the answer in the form of years.

The formula to compute the number of years since remodel at cell G8 is,

 =($H$2 - F8)/365.25

Copy the function used in the cell G8 to the cells G9:G57.

Step 3:

At cell H8 in the Summary worksheet, insert the function to compute the pet deposit.

Condition to check is whether the number of rooms that is at Cell C8 is greater than or equal to 2 and it is remodel less than 10 years ago.

If the condition is true, then pet deposit cost is $275 present at cell H4.

Otherwise the pet deposit cost is $200 present at cell H5.

So, the formula to apply at cell H8 is,

=IF(AND(C8>=2, G8<$H$3), $H$4, $H$5)

Step 4:

At cell I8 in the Summary worksheet, insert the function to display Need to Remodel:

Condition to check is whether the apartment is unoccupied depending on the cell E8(“Yes” or “No”) and last remodeled that is cell G8 is greater than 10 years (cell H3).

If the condition is true, then display as “No Change”.

Otherwise display as “Need to Remodel”.

So, the formula to apply at Cell I8 is,

=IF(_AND(_E8="No", G8>$H$3), "Need to Remodel", "No Change")

Step 5:

Copy the functions used in the cells H8:I8 to the cell range H9:I57.

Step 6:

At cell B3, to display the rental price:

By using Index function look up for the rental price present in Column D and check for the match of cell B2 with respective the Column Range A8:A57.

Syntax for INDEX function is:

=INDEX(Array, Row number, Column number)

Syntax for MATCH function is:

=MATCH(Loop up, Loop up array, Match type)

So, the function to be inserted at cell B3 is,

 =INDEX($D$8:$D$57, MATCH(B2, $A$8:$A$57,0))