Challenge Scenario
In sales analytics and customer relationship management (CRM), tracking customer activity timestamps is essential. When looking through a transaction ledger, you often need two distinct insights: the most recent transaction date across the entire company, and the most recent purchase date for a specific client account.
In this challenge, you are auditing five customer order transactions to extract key activity dates and total ledger revenue:
- Latest Global Date (Cell B9): Find the newest order date across all customer transactions using
MAX(A2:A6).
- Tesla Motors Most Recent Order (Cell B10): Find the last order date specifically for
"Tesla Motors" by performing a reverse bottom-to-top lookup using XLOOKUP("Tesla Motors", B2:B6, A2:A6, "None", 0, -1).
- Total Order Revenue (Cell B11): Sum the entire sales transaction volume across all logged orders using
SUM(C2:C6).
Your Sheet Layout
Here is how the customer order ledger and analysis summary blocks are structured:
| Cell Range |
Column / Label |
Section |
Formula / Approach |
What It Does |
A2:A6 |
Order Date |
Transaction Ledger |
Raw Date entries |
Dates when customer orders were placed. |
B2:B6 |
Customer Name |
Transaction Ledger |
Company account names |
Client accounts associated with each transaction. |
C2:C6 |
Sale Amount |
Transaction Ledger |
Monetary values ($) |
Transaction value for each order. |
B9 |
Latest Global Date |
Analysis Summary |
=MAX(A2:A6) |
Finds the newest transaction date across all rows. |
B10 |
Tesla Last Seen |
Analysis Summary |
=XLOOKUP("Tesla Motors", B2:B6, A2:A6, "None", 0, -1) |
Searches from bottom to top to return Tesla's latest order date. |
B11 |
Total Order Sum |
Analysis Summary |
=SUM(C2:C6) |
Calculates total sales revenue across all 5 orders ($8,300.00). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cell B9, find the latest order date from the full order log.
2
In cell B10, return the most recent order date for Tesla Motors.
3
In cell B11, calculate the total sum of all orders currently visible in the log.
Solution & Formula Breakdown
Let's understand how date evaluation, backward search lookups, and basic aggregations work step-by-step.
1. Finding the Newest Global Date (Cell B9)
=MAX(A2:A6)
Spreadsheet engines store calendar dates as chronological serial numbers (where newer dates have larger serial numbers). Because of this, the MAX function effortlessly returns the most recent date:
- The dates in the log range from
2024-11-20 up to 2026-04-02.
=MAX(A2:A6) evaluates to 2026-04-02 (Apple Tech's transaction).
2. Finding Tesla's Most Recent Order with Reverse XLOOKUP (Cell B10)
=XLOOKUP("Tesla Motors", B2:B6, A2:A6, "None", 0, -1)
Tesla Motors placed two separate orders in the ledger:
- Row 2:
2026-03-01 ($1,200.00)
- Row 4:
2026-04-01 ($800.00)
A standard top-to-bottom lookup would stop at Row 2 and return 2026-03-01. By providing -1 as the 6th parameter (search_mode), XLOOKUP searches from bottom to top, immediately catching Row 4 (2026-04-01)!
3. Aggregating Total Revenue (Cell B11)
=SUM(C2:C6)
Adding up all five order amounts (1200 + 4500 + 800 + 1500 + 300) yields 8300.00.
If you ever need to find the second or third most recent date instead of just the top one, you can use =LARGE(A2:A6, 2)!