Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. INDEX and MATCH

Last order date per customer: MAXIFS or XLOOKUP from the last row

  • Expert
  • ~10 min
  • INDEX and MATCH

The Orders sheet holds 400 orders placed in 2026, sorted by date with the oldest first; each row has the order date in column B and the customer in column C. Sales wants a churn watch-list on the Customers sheet: for each of the 30 accounts, the date of its most recent order. A plain lookup returns the first match, which here is the oldest order. You need the last one — either the latest date among that customer's orders, or the last matching row.

What you need to do

  1. 1On Customers, click C2. Option A (any Excel since 2019): =MAXIFS(Orders!$B$2:$B$401,Orders!$C$2:$C$401,A2) — the largest date among the rows whose customer is A2.
  2. 2Option B (Excel 365): =XLOOKUP(A2,Orders!$C$2:$C$401,Orders!$B$2:$B$401,,0,-1) — the 6th argument −1 searches from the last row upwards, so the first hit is the most recent order because the sheet is sorted by date.
  3. 3Click the Orders tab while typing to get the Orders! prefix, lock the ranges with F4 (Windows) or ⌘T (Mac), press Enter and fill C2 down to C31.
  4. 4Column C is pre-formatted as a date. If you see a number like 46333, the result is right — it is a date serial; apply a date format (Ctrl+1 / ⌘1) to any cell that shows one.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedOn the Customers sheet, in C2, return the most recent order date of the customer in A2 from the Orders sheet (dates in B2:B401, customers in C2:C401) — MAXIFS or XLOOKUP searching from the last row — then fill down to C31.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 430-row dataset on 2 sheets, one answer cell, about 10 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • Customers!C2=MAXIFS(Orders!$B$2:$B$401,Orders!$C$2:$C$401,A2)

Excel stores a date as a serial number — the count of days since 1 January 1900 — so "the most recent date" is simply the largest number. MAXIFS(dates, customers, A2) takes the maximum of the dates on rows whose customer equals the name in A2, which is the last order whatever the sort order of the sheet. XLOOKUP with search mode −1 reaches the same cell differently: it scans the customer column from the bottom and returns the date of the first match it meets, which is the latest order only because the Orders sheet is sorted oldest-first.

Pitfall 1: XLOOKUP(…,-1) depends on the sort order; a sheet sorted by order ID or by customer breaks it silently, MAXIFS does not care. Pitfall 2: MAXIFS returns 0 for a customer with no orders, which a date format shows as 1900-01-00; XLOOKUP returns #N/A. Wrap either in IF or IFNA when the list can contain prospects. Pitfall 3: the result is a serial number until the cell carries a date format — the grader compares numbers, so an unformatted correct answer still passes, but the report reader will not understand 46,250. Pre-2019 alternatives: =MAX(IF(Orders!$C$2:$C$401=A2,Orders!$B$2:$B$401)) entered with Ctrl+Shift+Enter, =LOOKUP(2,1/(Orders!$C$2:$C$401=A2),Orders!$B$2:$B$401) (the classic "last match" trick, sort-order dependent) and =AGGREGATE(14,6,Orders!$B$2:$B$401/(Orders!$C$2:$C$401=A2),1). Performance: MAXIFS scans 400 rows for each of 30 customers — trivial; on a million-row log a pivot table with Max of Order date is the better tool.

Other ways that work

  • =XLOOKUP(A2,Orders!$C$2:$C$401,Orders!$B$2:$B$401,,0,-1) — Excel 365; search mode −1 = last to first, relies on the date sort
  • =MAX(IF(Orders!$C$2:$C$401=A2,Orders!$B$2:$B$401)) — array formula (Ctrl+Shift+Enter before Excel 365)
  • =LOOKUP(2,1/(Orders!$C$2:$C$401=A2),Orders!$B$2:$B$401) — the classic last-match trick; also sort-order dependent
  • =AGGREGATE(14,6,Orders!$B$2:$B$401/(Orders!$C$2:$C$401=A2),1) — LARGE(…,1) ignoring the errors produced by non-matching rows

Common mistakes

A plain lookup: =XLOOKUP(A2,Orders!$C$2:$C$401,Orders!$B$2:$B$401) or a VLOOKUP returns the first matching row, which on a sheet sorted oldest-first is the customer's earliest order of the year, not the last.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel for Data AnalystsDay 10
  • Excel for Sales and MarketingDay 9

Related exercises

  • INDEX and MATCH exact lookupIntermediate, ~6 min
  • Two-way lookup in a matrixExpert, ~8 min
  • Two-criteria lookup (product and region)Expert, ~10 min
  • Replace VLOOKUP with XLOOKUPIntermediate, ~6 min

Function reference

Official documentation

Next exercise

Full name with CONCAT or &