Function Problems

How to Use Excel Conditional Formatting with XLOOKUP for Next Pay Date

Maira Mehtab Sep 21, 2026 869 views

Question details

The user wants to apply conditional formatting in Excel to highlight the pay date for the upcoming month but encounters a #NAME? error when using XLOOKUP. They also want to know how to adapt the formula for a two-column layout.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Highlighting upcoming monthly pay dates in a spreadsheet calendar or list.
Observed behavior: The XLOOKUP formula fails to highlight the correct date and returns a #NAME? error, especially when trying to adjust for the next month or across multiple columns.

Before you start

Ensure your version of Microsoft Excel (Office 365 or Excel 2021 and later) supports the XLOOKUP function, as older versions will return a #NAME? error.

Solution 1Recommended

Highlight the Next Pay Date Using XLOOKUP

Use the XLOOKUP function with the match mode set to find the next larger item to successfully highlight the upcoming pay date.

XLOOKUP is a powerful function that can search for a specific date or the next closest date. By using the TODAY() function as the lookup value and setting the match mode to 1, Excel will find the pay date that immediately follows today's date.

Select the Data Range

Highlight the range containing your pay dates, such as $A$4:$A$15.

Open Conditional Formatting

Navigate to the 'Home' tab, click on 'Conditional Formatting', and select 'New Rule'.

Choose Formula Option

Click on 'Use a formula to determine which cells to format'.

Enter XLOOKUP Formula

Type the formula: =XLOOKUP(TODAY(),$A$4:$A$15,$A$4:$A$15,,1)=$A4 in the formula box.

Apply Formatting

Click the 'Format' button, choose your preferred highlighting color from the Fill tab, and click 'OK' twice to apply the rule.

Understanding Match Mode: The '1' at the end of the XLOOKUP formula tells Excel to find the exact match or the next larger item, ensuring it picks the upcoming pay date even if today isn't a pay day.

Solution 2

Highlight the Following Month Using the MONTH Function

If your goal is simply to highlight any date that falls in the month immediately following the current month, the MONTH function provides a simpler alternative.

Solution 3

Fix the #NAME? Error and Handle Two-Column Layouts

Address compatibility issues causing the #NAME? error and adapt your formatting rules if your months are split across two columns.

Advanced Spreadsheet Tool

Highlight Future Dates Easily with WPS Spreadsheet

WPS Office offers a robust Spreadsheet application that fully supports modern functions like XLOOKUP. You can easily set up conditional formatting to track upcoming pay dates, deadlines, and schedules without expensive software subscriptions.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing file containing the pay dates.
2. Access Conditional Formatting: Highlight your date range and click on 'Conditional Formatting' under the 'Home' tab.
3. Create the Formula Rule: Select 'New Rule', then choose 'Use a formula to determine which cells to format'.
4. Apply Formatting: Input your XLOOKUP formula, pick an eye-catching fill color from the format settings, and click 'OK'.

Fully supports XLOOKUP, TODAY, and other advanced formulas.Seamless compatibility with Microsoft Excel (.xlsx) file formats.Intuitive Conditional Formatting interface for quick data visualization.Lightweight software with a familiar, easy-to-use workspace.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return a #NAME? error in my spreadsheet?

A #NAME? error occurs either because the function name is misspelled or your software version does not support XLOOKUP. It is only available in Microsoft 365, Excel 2021, and modern spreadsheet alternatives like WPS Office.

Can I apply this conditional formatting formula to highlight an entire row?

Yes. To highlight the entire row based on the pay date, select the entire data table before creating the rule, and ensure the column reference in the equality check is absolute (e.g., =$A4 instead of =A4).

How do I handle December when using the MONTH()+1 formula?

Adding 1 to December (Month 12) results in 13, which won't match January. Instead, use the EOMONTH function, such as =MONTH(A4)=MONTH(EOMONTH(TODAY(),1)), which accurately calculates transitions into the new year.

Does conditional formatting automatically update when the month changes?

Yes, because functions like TODAY() are volatile. The spreadsheet will automatically recalculate the formula and update the highlighting whenever you open the workbook or whenever a recalculation is triggered on a new day.

Excel & Data Problems