Function Problems

How to Find the Most Recent Changed Price in Excel using Formulas

Bushra Parveen Sep 25, 2026 870 views

Question details

The user needs an Excel formula to identify the most recent price that differs from the current price, ignoring any blank cells, and to return the corresponding date or month.

How to Find the Most Recent Changed Price in Excel using Formulas

Product: Excel
Device & OS: not provided
Scenario: Tracking product price changes over time in a horizontal data format and extracting the exact value and date of the last actual price change.
Observed behavior: The user wants to skip blank cells and identical consecutive prices to isolate the exact distinct previous value and its corresponding time period.

Before you start

Ensure your price data is organized chronologically in a single row or column, and verify that your version of Excel supports dynamic array functions like XLOOKUP and LET.

Solution 1Recommended

Use XLOOKUP to Find the Last Different Price and Date

XLOOKUP is the most efficient way to search backward through your data array to find the last non-blank, distinct price.

XLOOKUP features a built-in search mode that allows you to scan arrays from right to left (last to first). By multiplying conditional statements, you can force the function to only accept cells that are neither blank nor equal to the current price.

Select the target output cell

Click on the cell where you want the previous price to appear.

Enter the XLOOKUP formula for the price

Input the formula: =XLOOKUP(1,(A3:F3<>G3)*(A3:F3<>""),A3:F3,,,-1) where A3:F3 represents your historical price range and G3 is your current price cell. Press Enter.

Retrieve the corresponding date

To get the corresponding date or month, select the next cell and enter: =XLOOKUP(1,(A3:F3<>G3)*(A3:F3<>""),A2:F2,,,-1) assuming row 2 holds the dates. Press Enter to calculate.

Use XLOOKUP to Find the Last Different Price and Date

Search Mode Advantage: The '-1' at the end of the XLOOKUP formula explicitly instructs Excel to search from last-to-first, making it perfectly suited for chronological data.

Solution 2

Use the LET and INDEX Functions for Dynamic Spilling

If you want a dynamic array solution that spills both the date and the price into adjacent cells simultaneously, combining LET, LOOKUP, MAX, and INDEX is highly effective.

Advanced Formula Support

Use WPS Spreadsheet to Track Pricing Instantly

WPS Office Spreadsheet provides native support for advanced dynamic array functions like XLOOKUP and LET. You can track pricing history, execute complex criteria-based searches, and analyze financial data with zero friction.

1. Install WPS Office: Download and install WPS Office, then launch the Spreadsheet application.
2. Open Your Pricing Data: Click 'Open' to load your existing .xlsx workbook containing the product price history.
3. Apply the Search Formula: Select an empty cell and enter the =XLOOKUP() formula to search backward from the current date.
4. Analyze Results: Press Enter to instantly view the previously changed price and extract actionable insights from your data.

100% compatibility with Microsoft Excel formulas and data structuresFull support for modern dynamic array functions like XLOOKUP, LET, and FILTERLightweight footprint with lightning-fast calculation speedsCompletely free to use with an intuitive, familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return an #N/A error instead of the last price?

This usually happens if there are no distinct previous prices found that meet the criteria, or if the array sizes for the lookup and return ranges do not match exactly. Ensure your ranges (e.g., A3:F3) are perfectly aligned in size.

Can I use VLOOKUP or HLOOKUP to find the most recent previous price?

VLOOKUP and HLOOKUP can only search in one direction (left-to-right or top-to-bottom) and return the first match they encounter. They cannot search backward or easily handle multiple criteria like ignoring blanks and matching unequal values, which is why XLOOKUP or INDEX/MATCH must be used.

What does the -1 at the end of the XLOOKUP formula do?

The -1 is the 'search_mode' argument in the XLOOKUP function. It tells the formula to search from the last item to the first item (right-to-left or bottom-to-top). This is essential for chronological tracking when you need to find the most recent entry.

Excel & Data Problems