Function Problems

Return the Latest Dividend Transaction Amount in Excel

Steve KSteve K Nov 18, 2026 868 views

Question details

Return the amount from the latest dividend transaction for a specified stock.

Return the Latest Dividend Transaction Amount in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A holdings sheet needs to look up the latest dividend for a stock in a transaction sheet.
Observed behavior
The desired result is the amount from the most recent transaction whose stock matches the holding and whose transaction type is Div.
Before you start

Confirm that the transaction sheet has dates in column A, transaction types in B, stocks in C, and amounts in D. The formula below checks rows 1–3 and uses the stock in HoldingsDB!A5; adjust the ranges and cell reference to match your workbook, and ensure the dates are real Excel dates.

Solution 1Recommended

Use INDEX and MATCH to find the latest matching dividend

Use this formula to return the dividend amount by date, without sorting the transactions.

1
Select the result cell

On the HoldingsDB sheet, select an empty cell where you want the latest dividend amount to appear.

2
Enter the formula

In the selected cell, enter =IF(COUNTIFS('Transactions to HoldingsDB'!$C$1:$C$3,HoldingsDB!A5,'Transactions to HoldingsDB'!$B$1:$B$3,"Div")=0,"",INDEX('Transactions to HoldingsDB'!$D$1:$D$3,MATCH(1,('Transactions to HoldingsDB'!$C$1:$C$3=HoldingsDB!A5)*('Transactions to HoldingsDB'!$B$1:$B$3="Div")*('Transactions to HoldingsDB'!$A$1:$A$3=MAX(IF(('Transactions to HoldingsDB'!$C$1:$C$3=HoldingsDB!A5)*('Transactions to HoldingsDB'!$B$1:$B$3="Div"),'Transactions to HoldingsDB'!$A$1:$A$3))),0))).

3
Confirm and check the result

Press Enter in Microsoft 365 or Excel 2021. In older Excel versions, press Ctrl+Shift+Enter to confirm the array formula. Check that the returned amount matches the latest dividend for the stock in A5.

Use INDEX and MATCH to find the latest matching dividend
Matching dates and missing transactions: The formula returns a blank if there is no matching Div transaction. If multiple matching dividends share the latest date, it returns the first matching row in the specified range.
Calculate dividends in WPS Spreadsheet

Find the latest dividend amount in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use the same INDEX and MATCH formula to find the latest dividend for a holding.

  1. 1. Open the workbook: In WPS Spreadsheet, select Open and choose the workbook containing the Transactions to HoldingsDB and HoldingsDB sheets.
  2. 2. Check the source data: On Transactions to HoldingsDB, confirm that columns A through D contain dates, transaction types, stocks, and amounts. Check the stock reference in HoldingsDB!A5.
  3. 3. Enter the lookup formula: Select an empty result cell on HoldingsDB and paste the INDEX and MATCH formula from the solution above. Adjust all four ranges to cover the same transaction rows.
  4. 4. Confirm the calculation: Press Enter to calculate the formula. If your WPS Spreadsheet version requires array-formula entry, edit the cell and press Ctrl+Shift+Enter, then check the result against the transaction dates.
Find the latest matching dividend without sorting transactionsOpen and edit Microsoft Excel XLSX and XLS workbooksUse familiar spreadsheet formulas in a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if the stock has no dividend transactions?

The COUNTIFS check returns a blank when no row matches both the stock in HoldingsDB!A5 and the transaction type Div.

Do the transactions need to be sorted by date?

No. MAX finds the latest date among matching dividend transactions, so the rows can be in any order.

What if two dividends have the same latest date?

The formula returns the amount from the first matching row in the specified range. A date alone cannot distinguish which transaction occurred later on the same day.

How do I include more transaction rows?

Change each $1:$3 range in the formula to cover the same rows in columns A, B, C, and D. For example, change $A$1:$A$3 to $A$1:$A$1000 and extend the other three ranges to row 1000 as well.

Why is the formula not finding the latest date?

Check column A on the transaction sheet for real Excel dates rather than text, and verify that the stock and Div entries match the values used in the formula.