Formatting Issues

How to Match Lottery Numbers by Row Using Excel Conditional Formatting

Maira Mehtab Sep 27, 2026 869 views

Question details

The user wants to use conditional formatting to compare a range of played lottery numbers with drawn numbers on the exact same date and row without mistakenly matching numbers from other rows.

Product: Excel
Device & OS: not provided
Scenario: Comparing sets of numbers across the same row to find and highlight exact matches for specific dates.
Observed behavior: The current conditional formatting formula is referencing incorrect rows and dates because the relative references and applied ranges do not match the actual worksheet layout.

Before you start

Ensure your worksheet is organized systematically, with played numbers and drawn numbers clearly separated into specific columns on the exact same row for each corresponding date.

Solution 1Recommended

Use the COUNTIF Formula in Conditional Formatting

Apply a COUNTIF formula with properly locked column references to ensure Excel compares numbers strictly within the same row.

When highlighting matches row by row, the selected range and the formula references must align perfectly. If the formula uses incorrect absolute or relative references, Excel will highlight cells based on the wrong rows.

Select the target range

Highlight the range of cells containing your played numbers (for example, B4:F100).

Open Conditional Formatting

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

Enter the formula

Choose 'Use a formula to determine which cells to format'. Enter the formula =COUNTIF($K4:$O4,B4)>0, assuming K4:O4 contains the drawn numbers and B4 is the top-left cell of your highlighted range.

Apply formatting

Click the 'Format' button, choose your preferred fill color to highlight the matches, click 'OK', and then 'OK' again to apply the rule.

Understanding Reference Locking: Using the dollar sign ($) before the column letters ($K4:$O4) locks the comparison to those specific columns, while leaving the row number relative (4) allows the formula to adjust automatically as it checks each subsequent row.

Powerful Spreadsheet Tool

Easily Highlight Matching Data with WPS Spreadsheet

WPS Spreadsheet provides a seamless and intuitive interface for applying complex conditional formatting rules, allowing you to compare rows and highlight matching data effortlessly using standard formulas.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing your lottery numbers.
2. Select your data: Highlight the entire range of played numbers where you want the formatting applied.
3. Create a new rule: Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
4. Apply the COUNTIF formula: Select the formula option, enter =COUNTIF($K4:$O4,B4)>0, set your desired highlight color, and save.

100% compatible with Microsoft Excel file formats (.xlsx and .xls)Intuitive Conditional Formatting wizard for quick and accurate setupFree to use with comprehensive built-in formula support like COUNTIFLightweight application that runs smoothly on any operating system

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting incorrect cells?

This usually happens if the range you selected before applying the rule doesn't match the relative cell reference in your formula. Ensure the formula points exactly to the top-left cell of your selected range.

How do I adjust the formula if my dates span multiple rows?

If a single date spans multiple rows of played numbers, you must lock the row references for the drawn numbers using absolute references (e.g., $K$4:$O$4) so that all rows in that date group compare against the exact same drawn numbers.

Can I compare data across multiple worksheets?

Yes, you can reference another worksheet in your COUNTIF formula (e.g., =COUNTIF(Sheet2!$K4:$O4, B4)>0), provided the row layouts align correctly between the two sheets.

Excel & Data Problems