Highlight Rows in Excel Based on a Last-Name List
Question details
Highlight entire rows when an owner's last name appears in a fixed reference 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.
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.
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.
In Excel, select A2:D1000, or select your own table's data range without the header row.
Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
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.
Select Format, choose a fill color or another style, and click OK. Click OK again to create the rule.
Go to Home > Conditional Formatting > Manage Rules and confirm that Applies to covers the full table range, such as =$A$2:$D$1000.

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. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the last-name column and reference list.
- 2. Select the table: Select the data rows you want to highlight, such as A2:D1000.
- 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. Set the formatting: Choose a fill color or other format, confirm the rule, and check that the selected table range is covered.

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.




