I am trying to make a Lotto checklist. I have my own numbers in 6 columns A2:F2, and I have typed the winning numbers in H2:M2, 6 columns also. Now I want to check if my numbers in A2:F2 have any one or more of the numbers present in the winning numbers in H2:M2. And if there is a match, highlight the matched cell in red background and white text.

Any help is highly appreciated. Thank you.

1 Answer 1


Use this conditional formatting custom formula rule for the range A2:F2:

=countif($H2:$M2, A2)
  • Thanks. The formula worked. it got the two matched numbers in A2:F2. but I forgot to mention that H2:M2 has 1000 rows, my bad. Commented Feb 9, 2023 at 12:53
  • H2:M2 is a one-row range. It is possible to make a formula that checks A2:F2 against every row separately in the 999-row range H2:M1000, but that is a different question. Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Commented Feb 9, 2023 at 15:26
  • Sorry for that. I will correct my question and ask a new question instead. Thanks! Commented Feb 10, 2023 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.