# Condition formulas from text list to table

#### trekker1218

##### Board Regular
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Joe4

Try this:
2. Go to Conditional Formatting, select the Formula option, and enter this formula:
Code:
``=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.

Last edited:

#### DanteAmor

##### Well-known Member
Just another way:

Enter this formula in CF

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

Applies to:

=\$M\$2:\$AP\$500

#### trekker1218

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

thank you.

#### DanteAmor

##### Well-known Member
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.

1,102,049
Messages
5,484,398
Members
407,438
Latest member
DKrakken

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...