Function Problems

How to Use XLOOKUP to Return the Previous Available Date in Excel

Bushra Parveen Oct 9, 2026 869 views

Question details

The user needs to modify an XLOOKUP formula to fetch the most recent available date's data when the exact queried date (such as a weekend or holiday) is missing from the lookup table.

How to Use XLOOKUP to Return the Previous Available Date

Product: Excel
Device & OS: not provided
Scenario: Looking up daily rates where some dates are unavailable in the source data, requiring the formula to fall back to the most recent prior date.
Observed behavior: The formula needs to be configured to return an exact match if available, or the next smaller date (e.g., returning December 29 when December 30 or 31 is unavailable).

Before you start

Ensure your dataset's lookup array contains actual date values rather than text strings, and verify that the data allows the 'next smaller value' to logically represent the previous available date.

Solution 1Recommended

Change the XLOOKUP Match Mode to -1

Adjust the match_mode argument in your XLOOKUP formula to -1 so it finds an exact match or the next smaller item.

The XLOOKUP function contains an optional match_mode argument. By default, this is set to 0 (exact match). When dealing with dates where weekends or holidays are omitted, changing this argument to -1 tells the function to look for the exact date, and if it's missing, fall back to the largest value that is less than the lookup value (i.e., the previous available date).

Select the target cell

Double-click the cell containing your current XLOOKUP formula, or select it and click into the Formula Bar at the top of the worksheet.

Modify the XLOOKUP arguments

Navigate to the end of your required arguments (lookup_value, lookup_array, return_array). If you want to include a 'not found' message, type it as the fourth argument, such as "nf".

Set match_mode to -1

Add a comma and type -1 for the fifth argument (match_mode). Your final formula should look similar to: =XLOOKUP([@[Posting Date]],tbl_rate[Date],tbl_rate[Rate],"nf",-1). Press Enter to apply.

Understanding Match Mode -1: Using -1 ensures that if a user searches for Sunday, the formula will automatically return the data for Friday (the next smaller date in the dataset).

Advanced Spreadsheet Capabilities

Use Advanced Formulas Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced modern lookup functions like XLOOKUP. You can easily fetch previous available dates, handle complex data analysis, and manage large datasets efficiently.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date and rate tables.
2. Insert the XLOOKUP function: Click on the cell where you want the result, type =XLOOKUP(, or use the Insert Function button (fx) to open the Function Arguments dialog.
3. Input basic arguments: Select your Lookup_value (the target date), Lookup_array (the column of available dates), and Return_array (the column of rates).
4. Configure the match mode: In the Match_mode field, enter -1 to ensure WPS Spreadsheet returns the previous available date, then click OK or press Enter.

Fully compatible with Microsoft Excel formulas, including XLOOKUP and VLOOKUPLightweight and fast data processing for large worksheetsBuilt-in function wizard helps you build complex formulas without memorizing syntaxFree to use for everyday spreadsheet and data lookup tasks

Frequently Asked Questions

What does the match_mode -1 do in an XLOOKUP formula?

The -1 match mode instructs the function to search for an exact match first. If an exact match is not found, it returns the next smaller item. For dates, which Excel stores as sequential numbers, the 'next smaller item' is the most recent previous date.

Can I use XLOOKUP to find the next available future date instead?

Yes. If you want to find the next available date (the next larger item) instead of the previous one, you can set the match_mode argument to 1.

Why is my XLOOKUP formula returning a #VALUE! error?

A #VALUE! error typically occurs if your lookup_array and return_array are different sizes. Ensure both arrays cover the exact same number of rows. Also, check that your dates are formatted as valid numbers or dates, not as text.

What is the difference between match mode and search mode in XLOOKUP?

Match mode (the 5th argument) dictates what happens if an exact match isn't found (like picking the next smaller or larger item). Search mode (the 6th argument) determines the search direction, such as searching from first-to-last or last-to-first.

Excel & Data Problems