Forum Discussion

SeaStar91's avatar
SeaStar91
Copper Contributor
Feb 14, 2023

How to Find and Highlight Exact Match in Column

I am tracking cars that park without permission in a private lot and need a way to identify and highlight  the same license plate number (Column B data) for a quick comparison to determine if there is a prior violation. 

 

Ideally, the match(es) get(s) identified and highlighted as I type the license plate number into the new entry. 

 

Anyone know a formula or function that will get me what I'm looking for?

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 14, 2023

    SeaStar91 

    =IF(COUNTIF($B$2:B2,B2)>1,MATCH(B2,$B$2:B2,0)+1,"")

    This is the formula in cell C2 which returns the row of a previous entry.

    =COUNTIF($B$2:$B$28,B2)>1

    This is the rule for conditional formatting.

    =$B$2:$B$28

    The format is applied to this range in the example.

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Feb 14, 2023

    SeaStar91 

    Here is my solution:

    Conditional Formarting:

     

    =AND(B1=XLOOKUP("*",B1:B1000,B1:B1000,FALSE,2,-1),ROW()<>XLOOKUP("*",B1:B1000,ROW(B1:B1000),FALSE,2,-1),B1<>"")

    Only the duplicates of the last entry are marked.

    See if that fits.

     

Resources