Function Problems

How to Return the Amount from the Latest Transaction in Excel

Guest Writer Sep 28, 2026 869 views

Question details

The user needs a formula to look up and retrieve the transaction amount that corresponds to the most recent transaction date for each unique customer.

Excel Formula to Return the Amount from the Latest Transaction

Product: Excel
Device & OS: not provided
Scenario: Extracting the latest transaction data for individual customers from a comprehensive sales or transaction log containing multiple chronological entries per customer.
Observed behavior: Standard lookup formulas typically return the first match they encounter, whereas the user specifically needs to retrieve data associated with the maximum date value for each unique customer ID.

Before you start

Ensure your transaction data is organized in clear columns (e.g., Customer ID in Column A, Amount in Column B, Date in Column C) and verify that the date column is formatted as actual dates rather than text.

Solution 1Recommended

Use XLOOKUP and MAXIFS for Modern Excel (Microsoft 365)

This recommended method leverages dynamic arrays and modern functions to cleanly extract unique customers, find their latest date, and return the matching amount.

If you are using Microsoft 365, modern dynamic-array functions make retrieving conditional multi-criteria data significantly easier and less prone to errors compared to legacy formulas.

Extract Unique Customers

Select a blank cell (e.g., E2) and enter the formula =SORT(UNIQUE(A2:A17)) to automatically generate a sorted list of distinct Customer IDs from your data range.

Calculate the Latest Date

In the adjacent cell (F2), type the formula =MAXIFS($C$2:$C$17, $A$2:$A$17, E2#) to find the maximum (most recent) transaction date associated with the respective customer.

Retrieve the Corresponding Amount

In cell G2, use the formula =XLOOKUP(E2&F2, $A$2:$A$17&$C$2:$C$17, $B$2:$B$17) to match both the customer ID and the latest date, securely extracting the correct transaction amount.

Use XLOOKUP and MAXIFS for Modern Excel (Microsoft 365)

Dynamic Array Spill: Because these formulas use array references like E2#, they will automatically "spill" down the column to calculate the latest amounts for all unique customers generated in the first step.

Solution 2

Use the LOOKUP Function for Older Excel Versions

If you do not have Microsoft 365 or XLOOKUP support, you can use the traditional LOOKUP function with an array division trick to find the last record in a chronologically sorted list.

Advanced Spreadsheets

Effortlessly Manage Financial Data with WPS Spreadsheet

WPS Office provides a fully featured Spreadsheet program that natively supports advanced modern formulas like XLOOKUP, UNIQUE, and MAXIFS, making complex data retrieval tasks fast and simple.

1. Open Your Transaction Data: Launch WPS Spreadsheet and open the document containing your transaction logs.
2. Apply Dynamic Array Formulas: Select an empty cell and enter the =UNIQUE(A2:A17) formula to automatically populate your unique customer list.
3. Calculate Latest Amounts: In the adjacent columns, apply the =MAXIFS and =XLOOKUP formulas exactly as you would in Microsoft Excel.
4. Review the Results: Press Enter, and WPS Spreadsheet will instantly calculate and display the latest transaction amounts.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Native support for modern dynamic array functions like XLOOKUP, SORT, and UNIQUE.Lightweight installation and smooth performance even with large transaction datasets.Free to use with an intuitive, familiar interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #N/A error when searching for the latest transaction?

This usually happens if the concatenated lookup value (Customer ID & Date) doesn't exactly match the concatenated lookup array. Ensure both columns are formatted consistently and check for hidden trailing spaces in the Customer ID column.

Can I use VLOOKUP instead to find the latest transaction amount?

VLOOKUP typically returns the first match it encounters scanning from top to bottom. To use VLOOKUP for the latest transaction, you must sort your data in descending order by date so the newest transaction appears first, then run a standard VLOOKUP.

How do I fix the LOOKUP formula returning the wrong transaction amount?

The LOOKUP(2, 1/(Criteria), Result) formula relies on the physical order of rows in your spreadsheet to find the final match. If it returns an incorrect amount, ensure your raw dataset is sorted chronologically by the transaction date from oldest to newest.

Excel & Data Problems