Google sheets conditional format to highlight a column containing items from another list

Question

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

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.