Function Problems

Highlight Rows in Excel Based on a Last-Name List

Steve KSteve K Nov 18, 2026 869 views

Question details

Highlight entire rows when an owner's last name appears in a fixed reference list.

Highlight Excel Rows Based on a Last-Name List
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains owner last names and a separate list of surnames to match.
Observed behavior
Rows with last names found in the reference list should receive the chosen formatting.
Before you start

Identify the last-name column, the first data row, and the cells containing the reference list. In the example below, data is in A2:D1000, last names are in column B, and the reference list is in Z1:Z100.

Solution 1Recommended

Create a Conditional Formatting Rule with MATCH

Use a formula to check each row's last name against a fixed surname list and format the full row.

1
Select the data rows

In Excel, select A2:D1000, or select your own table's data range without the header row.

2
Create a formula-based rule

Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.

3
Enter the matching formula

Enter =ISNUMBER(MATCH($B2,$Z$1:$Z$100,0)). Replace B with your last-name column and Z1:Z100 with your reference-list range. The row number in $B2 must match the first row of your selection.

4
Choose the row format

Select Format, choose a fill color or another style, and click OK. Click OK again to create the rule.

5
Check the applied range

Go to Home > Conditional Formatting > Manage Rules and confirm that Applies to covers the full table range, such as =$A$2:$D$1000.

Create a Conditional Formatting Rule with MATCH
What the match means: The rule identifies names that appear in your list; a surname alone does not establish a person's ethnicity.
Highlight matching rows in WPS

Highlight Rows from a Surname List in WPS Spreadsheets

Use a formula-based conditional formatting rule in WPS Spreadsheets to compare each row's last name with a fixed reference list.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the last-name column and reference list.
  2. 2. Select the table: Select the data rows you want to highlight, such as A2:D1000.
  3. 3. Add a formula rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula. Enter =ISNUMBER(MATCH($B2,$Z$1:$Z$100,0)), adjusting the references for your sheet.
  4. 4. Set the formatting: Choose a fill color or other format, confirm the rule, and check that the selected table range is covered.
Highlights entire rows when a last name matches the reference listOpens and saves Microsoft Excel XLSX filesUses familiar spreadsheet formulas and formatting controlsKeeps the reference-list range fixed as the rule checks each row
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use $B2 instead of $B$2?

The dollar sign fixes column B, while the row number can change as the rule evaluates each row. Using $B$2 would check the same cell for every row.

How do I use a different surname list?

In the conditional formatting formula, replace $Z$1:$Z$100 with your list's actual range, such as $H$2:$H$50. Keep the dollar signs to fix the range.

Why isn't a name being highlighted?

Check that the formula's row number matches the first row in Applies to and that the last-name column is correct. Also check for extra spaces or spelling differences between the table and the reference list.

Does MATCH distinguish between uppercase and lowercase names?

No. MATCH with 0 for the match type is not case-sensitive, so names that differ only in capitalization can match.