ThisCalculator

Everyday

Highlight Many Matching Values: How to Do It

Updated · Standard formula, shown below

Average pace 5.00 min/km
Average speed
12.00 km/h
Result
Finish time
50 min
You entered
Distance
10 km
You entered

This article explains how to highlight many matching values in a spreadsheet, such as Google Sheets or Microsoft Excel. You’ll learn the exact formulas and steps to automatically color every cell that matches a value from a separate list, and how to avoid common mistakes.

Formula

The core formula for highlighting matching values uses a conditional formatting rule with a COUNTIF or MATCH function. In plain words: if the count of a cell’s value in the comparison list is greater than zero, the cell gets highlighted. The formula is:

=COUNTIF($B$2:$B$10, A2) > 0

Where:

  • A2 – the first cell in the range you want to highlight (the “target” range).
  • $B$2:$B$10 – the list of values you want to match against (the “criteria” range). Use absolute references with $ so the range doesn’t shift.

An alternative using MATCH: =ISNUMBER(MATCH(A2, $B$2:$B$10, 0)). Both work; COUNTIF is easier to read for most users.

Worked Example

Suppose you have a list of product IDs in column A (A2:A20) and a separate list of “approved” IDs in column B (B2:B5). You want to highlight any product ID in column A that appears in column B.

  1. Select the range A2:A20.
  2. Open the conditional formatting menu (in Google Sheets: Format → Conditional formatting; in Excel: Home → Conditional Formatting → New Rule).
  3. Choose “Custom formula is” (Sheets) or “Use a formula to determine which cells to format” (Excel).
  4. Enter: =COUNTIF($B$2:$B$5, A2) > 0
  5. Set a fill color (e.g., light green) and click Done.

For the default example, if A2 contains “ID-100” and B2:B5 contains “ID-100”, the cell A2 will be highlighted. If A2 contains “ID-200” which is not in the B list, it will not be highlighted. The formula counts how many times the value in A2 appears in the B range; if that count is more than 0, the condition is true.

When to Use This Technique

Highlighting matching values is useful in many real-world scenarios:

  • Comparing two columns of customer emails to find duplicates or overlaps.
  • Marking which inventory items are on a reorder list.
  • Flagging names on a guest list that have already checked in.
  • Identifying which students in a class are on a scholarship list.

The technique works for text, numbers, dates, and even partial matches if you use wildcards (e.g., =COUNTIF($B$2:$B$10, "*"&A2&"*") > 0).

How to Choose the Right Formula

Both COUNTIF and MATCH have pros and cons. COUNTIF is straightforward and handles wildcards easily. MATCH is slightly faster on very large datasets (thousands of rows) because it stops at the first match. For most users, COUNTIF is the better choice because it’s easier to debug. If you need to match case-sensitively, use =SUMPRODUCT(--EXACT(A2, $B$2:$B$10)) > 0 in Excel; in Google Sheets, use =ARRAYFORMULA(SUM(--EXACT(A2, $B$2:$B$10))) > 0.

Common Problems and Fixes

Many users run into the same issues when setting up this rule:

  • Wrong range references: If you forget the $ signs, the formula shifts as it applies to each cell, causing false matches. Always lock the criteria range with $.
  • Leading/trailing spaces: A value like “apple” and ” apple ” won’t match. Use TRIM() in a helper column or in the formula: =COUNTIF($B$2:$B$10, TRIM(A2)) > 0 (though this requires the criteria range to be trimmed too).
  • Number vs. text: If one column contains numbers and the other contains text (e.g., 123 vs “123”), they won’t match. Convert one column using TEXT() or VALUE().
  • Applying to the wrong range: The rule applies to the range you selected before opening the menu. If you select A2:A20, the formula should reference A2 (the first cell), not A1.

Comparing Google Sheets and Excel

The steps are almost identical, but there are minor differences. In Google Sheets, you choose “Custom formula is” and the formula must be entered without the equals sign? Actually, you do include the equals sign. In Excel, you choose “Use a formula” and the formula also starts with =. The main difference is the menu layout. Also, Excel has a “Manage Rules” dialog that lets you edit multiple rules at once; Sheets has a similar panel on the right. Both support the same COUNTIF and MATCH functions.

Limitations and Alternatives

The COUNTIF method works well for exact matches. If you need to match partial text (e.g., a list of substrings), you can use wildcards, but be careful with performance on large datasets. For fuzzy matching (e.g., names with slight variations), you may need a more advanced approach like a custom script or a separate lookup column. Also, conditional formatting only changes the appearance; it doesn’t filter or extract the matching rows. To extract matches, use FILTER() in Sheets or Advanced Filter in Excel.

Frequently Asked Questions

Can I highlight entire rows based on a match?

Yes. Instead of selecting a single column, select the entire data range (e.g., A2:D20) and use the same formula, but reference the first row’s key cell (e.g., =COUNTIF($B$2:$B$10, $A2) > 0). The $ before the column letter locks the column, so the formula applies to each row independently.

Why is my formula highlighting blank cells?

If your criteria range contains blanks, COUNTIF will count them, and any blank cell in your target range will match. Add a condition to exclude blanks: =AND(A2<>"", COUNTIF($B$2:$B$10, A2) > 0).

How do I highlight values that are NOT in the list?

Simply change the condition to =COUNTIF($B$2:$B$10, A2) = 0. This highlights cells that don’t match any value in the criteria range.

Can I use this with dates?

Yes, COUNTIF works with dates. Ensure both columns are formatted as dates, not text. If you’re comparing date ranges, use COUNTIFS with two criteria.

Does this work with duplicate values in the criteria list?

Yes, COUNTIF counts all occurrences, so even if a value appears twice in the criteria range, the condition is still true. The result is the same.

Related

Formula used
in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.