Function Problems

How to Highlight an Excel Cell Based on the Latest Value in Another Column

Natalie Taylor Sep 28, 2026 869 views

Question details

The user needs to use conditional formatting to highlight a specific cell in Column K if its value matches the most recently entered (newest) non-blank value in Column H.

Highlight an Excel Cell Based on the Latest Value in Another Column

Product: Excel / WPS Spreadsheets
Device & OS: not provided
Scenario: Setting up a dynamic data tracking sheet where the target highlight updates automatically as new entries are added to a reference column.
Observed behavior: The cell formatting should automatically locate the last populated cell in Column H, skipping any blanks or headers, and apply the highlight to the corresponding matching value in Column K.

Before you start

Ensure your dataset does not contain merged cells in the target columns, and note the exact starting row of your data (e.g., Row 3) so your conditional formatting formula aligns correctly.

Solution 1Recommended

Use the LOOKUP Function to Handle Data with Blanks

This is the most robust solution. The LOOKUP formula accurately finds the last non-blank cell in a column, ignoring empty rows and headers.

By dividing 1 by an array of TRUE/FALSE values (where the cell is not blank), the LOOKUP function can be forced to return the very last valid entry in a column. This perfectly handles real-world data where some cells might be skipped.

Select the target range

Click and drag to select the target range in Column K where you want the highlight to appear, such as K3:K1000.

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 the option 'Use a formula to determine which cells to format'. In the formula box, type: =K3=LOOKUP(2,1/(H:H<>""),H:H). Make sure 'K3' matches the first row of your selected range.

Apply formatting

Click the Format button, choose your desired fill color (e.g., yellow) to highlight the cell, and click OK to apply the rule.

Use the LOOKUP Function to Handle Data with Blanks

Formula Breakdown: The expression 1/(H:H<>"") creates an array of 1s and divide-by-zero errors. Looking up '2' forces Excel to find the last '1' in the array, retrieving the newest entry in Column H.

Solution 2

Use INDEX and COUNTA for Continuous Data

Use this alternative formula if your reference column has continuous data without any blank cells.

Solution 3

Highlight Based on Corresponding Row Value

Use this simpler rule if you merely want to compare each cell in Column K to the cell directly next to it in Column H.

Dynamically Format Spreadsheets with WPS Office

WPS Spreadsheets fully supports advanced conditional formatting formulas, including LOOKUP and INDEX functions. You can easily highlight dynamic data and track your newest entries without any compatibility issues.

1. Open your workbook: Launch WPS Spreadsheets and open the file containing your data columns.
2. Select your column: Highlight the target cells in Column K where the formatting should appear.
3. Access Conditional Formatting: Go to the Home tab on the ribbon, click Conditional Formatting, and select New Rule.
4. Apply the dynamic formula: Choose the formula option, paste your LOOKUP formula, set your fill color, and click OK.

Fully compatible with Microsoft Excel conditional formatting rulesSupports complex array and LOOKUP formulas seamlesslyLightweight application with a highly intuitive tabbed interfaceFree built-in data tracking and formatting templates

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong row?

This usually happens when the row number in your formula does not match the first row of your selected range. For example, if you highlight K3:K1000 but your formula says =K1=..., the highlighting will be shifted by two rows. Ensure the formula always references the active starting cell.

Can I highlight the entire row instead of just the cell in Column K?

Yes. To highlight the entire row, select the entire dataset (e.g., A3:Z1000) instead of just Column K. Then, lock the column references in your formula by adding a dollar sign, like this: =$K3=LOOKUP(2,1/($H:$H<>""),$H:$H).

Does the LOOKUP formula update automatically when I add new data?

Yes. Because the formula references the entire column (H:H), any time you type a new value at the bottom of Column H, the conditional formatting immediately recalculates and shifts the highlight to the new matching value in Column K.

What if the newest value in Column H appears multiple times in Column K?

The conditional formatting formula =K3=... checks every cell in your selected range. If the newest value in Column H is '7' and the number '7' appears in K5, K20, and K50, all three of those cells will be highlighted.

Excel & Data Problems