Return the Latest Dividend Transaction Amount in Excel
Question details
Return the amount from the latest dividend transaction for a specified stock.

- 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.
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.
Use INDEX and MATCH to find the latest matching dividend
Use this formula to return the dividend amount by date, without sorting the transactions.
On the HoldingsDB sheet, select an empty cell where you want the latest dividend amount to appear.
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))).
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.

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. Open the workbook: In WPS Spreadsheet, select Open and choose the workbook containing the Transactions to HoldingsDB and HoldingsDB sheets.
- 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. 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. 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.

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.




