Financial & Accounting
Retrieve Latest Closing Price, As-Of Quote & Trailing N-Day Average From a Price Log
Extract the most recent closing price for any ticker in an unsorted multi-symbol quote log, snap weekend or holiday valuation dates back to the prior trading close, and compute trailing N-day averages.
Sort-independent latest close and weekend-safe historical valuation
Portfolio trackers often append daily closing quotes across multiple symbols in a single ledger (Date, Ticker, Close). Using VLOOKUP or XLOOKUP(..., -1) directly on Ticker returns whichever row happens to sit first or last physically rather than the chronologically latest date—and querying a Sunday month-end date returns #N/A because markets were closed.
Finding the exact latest trading date for symbol F2 on or before valuation date G2 via asOfDt = MAXIFS(A2:A7, B2:B7, F2, A2:A7, "<=" & G2) works regardless of row order and automatically snaps Sunday 2026-05-10 back to Friday 2026-05-08. Retrieving the matching price via SUMIFS(C2:C7, B2:B7, F2, A2:A7, asOfDt) (for unique daily ticker closes) completes the lookup cleanly.
| Trade Date | Ticker | Close Price | Query Against NVDA |
|---|---|---|---|
| 2026-05-01 (Fri) | NVDA | $118.50 | Prior week |
| 2026-05-04 (Mon) | MSFT | $422.00 | Other symbol ignored |
| 2026-05-05 (Tue) | NVDA | $121.25 | Prior close |
| 2026-05-08 (Fri) | NVDA | $124.00 | As-of close for Sun 2026-05-10 ($124.00) |
| 2026-05-12 (Tue) | NVDA | $126.75 | Latest overall close ($126.75) |
Latest Overall Closing Price for Selected Ticker (Sort-Order Independent)
Finds the maximum trade date for ticker F2 in A2:C7 and returns its closing price, or "Ticker Not Found" if absent.
=LET(lastDt, MAXIFS(A2:A7, B2:B7, F2), IF(lastDt = 0, "Ticker Not Found", SUMIFS(C2:C7, B2:B7, F2, A2:A7, lastDt)))Weekend-Safe As-Of Close & Trailing N-Day Mean Close
Resolves the latest closing price for ticker F2 on or before valuation cutoff date G2.
=LET(asOfDt, MAXIFS(A2:A7, B2:B7, F2, A2:A7, "<=" & G2), IF(asOfDt = 0, "No Prior Close", SUMIFS(C2:C7, B2:B7, F2, A2:A7, asOfDt)))Try the example
Use the original values or change them to test your understanding.