Which nested INDEX and MATCH formula returns the ending balance for the date in C8 using the named range Schedule for the loan payments and PayDates for the payment date column, with Ending Balance as column 6?

Which nested INDEX and MATCH formula returns the ending balance for the date in C8 using the named range Schedule for the loan payments and PayDates for the payment date column, with Ending Balance as column 6?

• A. =INDEX(PayDates, MATCH(C8, Schedule, 0), 6)
• B. =MATCH(C8, PayDates, 0)
• C. =INDEX(Schedule, MATCH(C8, PayDates, 0), 6)
• D. =INDEX(Schedule, C8, 6)

Answer: (C)

The formula uses a two-step lookup: find the row that corresponds to the date in the cell, then pull the value from the Ending Balance column in that same row. Schedule is the 2D table containing all loan payment data, and Ending Balance sits in its sixth column. By matching the date in C8 against the PayDates column, you get the exact row. INDEX then returns the value from that row in column six. So the correct approach is to use the array Schedule with a row determined by MATCH(C8, PayDates, 0) and the column number 6, giving: =INDEX(Schedule, MATCH(C8, PayDates, 0), 6).

The formula uses a two-step lookup: find the row that corresponds to the date in the cell, then pull the value from the Ending Balance column in that same row. Schedule is the 2D table containing all loan payment data, and Ending Balance sits in its sixth column. By matching the date in C8 against the PayDates column, you get the exact row. INDEX then returns the value from that row in column six. So the correct approach is to use the array Schedule with a row determined by MATCH(C8, PayDates, 0) and the column number 6, giving: =INDEX(Schedule, MATCH(C8, PayDates, 0), 6).