Condition formulas from text list to table

[trekker1218]

Hello,
I have a list of text data in cell AQ2:AQ430
i need to find every occurrence of that list across cell M2:AP500
Then Highlight the matches only.
I have gone through plenty of permutations of the condition formulas most have suggested with no good results.
tried index
countif
isna
vlookup

formulas and cant get it to what I want. Some formulas just highlight ALL items, some highlight some but not all items in AQ2:AQ430

any help would be appreciated.
A VBA would be fine if it works too.

thank you all again.

 

[Joe4]

Try this:
1. Select your range M2:AP500
2. Go to Conditional Formatting, select the Formula option, and enter this formula:

=COUNTIF($AQ$2:$AQ$430,M2)>0

3. Select your desired highlight color
4. Click OK

All values in M2:AP500 that are in the list AQ2:AQ430 should now be highlighted.

 

[DanteAmor]

Just another way:

Enter this formula in CF

=MATCH(M2,$AQ$2:$AQ$430,0)

Applies to:

=$M$2:$AP$500

 

[trekker1218]

Thank you. I had that same formula and missed the >0 part.

thank you.

 

[DanteAmor]

It also reviews the absolute reference:
$AQ$430

 

[Joe4]

It also reviews the absolute reference:
$AQ$430

Good catch, Dante.
I went back and updated the formula.