I have rows of data, Col. A will be the order number and there will be duplicates of the order number (this is for each individual item purchase and MUST NOT be removed to give unique numbers). Then I would have another list of order numbers, these are the ones that I wanted highlighted in the main sheet.

In my spreadsheet, Finding matches sheet contains the order numbers as they would appear in the real file & the List tab contains some order numbers - these ones I want highlighted in the first sheet.

After doing some google research, I've tried these formulas, via conditional format -> formula. None worked to like I wanted them to. The most recent one I tried is =VLOOKUP(List!A2:A10,A2:A90,1,FALSE). =ArrayFormula(countif($A$2:$A,$B2:$B))>0 this one too didn't work.

Here's the sample sheet: https://docs.google.com/spreadsheets/d/1W8L445SfSEIrrPgwPaPryC72DK_zwvyCbU6ot1ezsYM/edit#gid=275309229

1 Answer 1


Use indirect() to refer to another sheet in a conditional formatting custom formula rule, like this:

=countif(indirect("List!A2:A"), A2)

See your sample spreadsheet.

  • thank you so much
    – Genny
    Aug 25, 2021 at 11:38

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.