Formatting Issues

How to Highlight Excel Cells When Names Match Another Column

Aamir Naveed Akram Oct 16, 2026 868 views

Question details

The user needs to use conditional formatting to highlight names in a target range (such as columns F:H) if they appear anywhere in a reference column (like column V), while specifically ignoring cells that contain words like VACANT.

How to Highlight Excel Cells When Names Match Another Column

Product: Excel
Device & OS: not provided
Scenario: Comparing lists of names or data across different columns and visually identifying overlapping entries automatically without modifying the original reference data.
Observed behavior: When set up correctly, cells in the target range are highlighted if a matching value is found in the reference column, while non-target words remain unformatted.

Before you start

Ensure that your data does not have hidden trailing spaces, as formula-based conditional formatting requires exact text matches to function properly. You may want to use the TRIM function on your columns if data was imported from another system.

Solution 1Recommended

Use COUNTIF Formula to Find Matches Anywhere in the Reference Column

This is the most reliable approach for highlighting cells that appear anywhere in a target column while intentionally excluding specific text values.

The COUNTIF function checks if the value in your selected cell appears at least once in the target column. Combining this with the AND function lets you add conditions, such as ignoring cells with the word 'VACANT'.

Select the target range

Highlight the specific cells you want to format, for example, columns F through H (e.g., F1:H100).

Open Conditional Formatting

Go to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the drop-down menu.

Choose the formula option

In the dialog box, click on 'Use a formula to determine which cells to format'.

Enter the formula

Type the formula =AND(F1<>"VACANT",COUNTIF($V:$V,F1)>0). Make sure to replace 'F1' with the active cell of your current selection (the top-left cell in your highlighted range).

Apply a highlight format

Click the Format button, go to the Fill tab to choose a background color for the matched cells, click OK, and then click OK again to apply the rule.

Use COUNTIF Formula to Find Matches Anywhere in the Reference Column

Cell Referencing Matters: Ensure you use absolute referencing ($V:$V) for the column you are searching, but relative referencing (F1) for the cell you are checking, otherwise the formatting will apply incorrectly.

Solution 2

Highlight Matches Only on the Exact Same Row

Use this method if you only want to highlight names when the reference column has the identical name on the exact same row.

Easily Cross-Reference Column Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting rules and formulas, allowing you to seamlessly cross-reference columns, highlight matching data, and exclude specific text values just like Microsoft Excel.

1. Open your spreadsheet: Launch WPS Spreadsheet and select the target range you want to format (e.g., columns F:H).
2. Access Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and select New Rule.
3. Apply the cross-reference formula: Select 'Use a formula to format cells', input =AND(F1<>"VACANT",COUNTIF($V:$V,F1)>0), choose a highlight color in the format menu, and click OK.

100% compatible with Microsoft Excel conditional formatting rulesFree, lightweight, and fast spreadsheet processing toolIntuitive formula builder for logical functions like AND, COUNTIF, and VLOOKUPIdentical user interface layout, requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows or cells?

This usually happens due to absolute and relative reference mismatches. Ensure that the formula references the top-left cell of your selection without dollar signs (e.g., F1) so it updates for each cell, while the reference column uses absolute referencing (e.g., $V:$V) so it remains locked.

How do I ignore blank cells when using the COUNTIF formula for highlighting?

You can update the AND function in your conditional formatting formula to exclude empty cells. For example, use =AND(F1<>"",F1<>"VACANT",COUNTIF($V:$V,F1)>0). The F1<>"" part ensures blanks are skipped.

Can I highlight the entire row instead of just the single matching cell?

Yes. To highlight an entire row based on a matching cell, select the whole data table instead of just the target column. Then, lock the target cell's column with a dollar sign in your formula (e.g., =$F1 instead of F1) so the entire row follows the logic of that specific column.

Document & File Issues