Lookup FunctionsIntermediate

Name the Busiest and Quietest Half-Hour with INDEX, MATCH and MAX

MAX tells you how many calls the busiest half-hour took, but the rota needs to know which half-hour it was. Feeding MAX into MATCH finds where that number sits in the column, and INDEX turns that position into the label beside it.

Task:

You run the reception desk at a doctors' surgery, and last Monday's phone log, the calls received in each half-hour from 8:00 to 13:00, is in A2:B11. You want an extra receptionist on the phones for the worst half-hour and the team huddle in the quietest one. In E2, return the time slot that received the most calls, and in E3, the slot that received the fewest. Both answers should be the slot label from column A, not a number of calls, and should still be right when next Monday's figures are pasted over these.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

11 rows × 5 columns2 cells you fill in
ABCDE
1Time slotCalls received
208:00-08:3096Busiest slot
308:30-09:00118Quietest slot
409:00-09:3087
509:30-10:0064
610:00-10:3049
710:30-11:0041
811:00-11:3035
911:30-12:0038
1012:00-12:3022
1112:30-13:0027
What this exercise teachesMay contain the answer

The question runs the opposite way to the sheet. The slots are labels and the calls are measurements, and what you know is which measurement you want, the largest, not which slot. MAX(B2:B11) finds that number, 118, but on its own it only says how bad the peak was, not when it came. VLOOKUP cannot finish the job: it searches the first column of its table and reads to the right, and here the number being searched for is in B while the answer is to its left in A, so =VLOOKUP(MAX(B2:B11),A2:B11,1,FALSE) looks for 118 among the time labels and returns #N/A. INDEX and MATCH split the job in two and do not care which side the answer is on. MATCH(MAX(B2:B11),B2:B11,0) looks for 118 among the call counts and returns 2, because it is the second value in the range; the 0 asks for an exact match, which matters because the counts are not in order. INDEX(A2:A11,2) then returns the second slot label, 08:30-09:00. That only works because both ranges start on row 2 and are the same height, so position 2 in one is the same row as position 2 in the other; start one range a row out and every answer slides to the neighbouring slot. Swapping MAX for MIN finds 22 and returns 12:00-12:30. Nothing in either formula mentions 118 or 22, so when next Monday's log is pasted over this one the answers follow it. One limit worth knowing: if two slots tie for the most calls, MATCH stops at the first one it meets, so the formula names the earlier slot and says nothing about the other.