Condition formulas from text list to table

trekker1218

Board Regular
Joined
Feb 15, 2018
Messages
67
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
Try this:
1. Select your range M2:AP500
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
Joined
Dec 3, 2018
Messages
12,199
Office Version
2007
Platform
Windows
Just another way:

Enter this formula in CF

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

Applies to:

=$M$2:$AP$500
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,199
Office Version
2007
Platform
Windows
It also reviews the absolute reference:
$AQ$430
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
It also reviews the absolute reference:
$AQ$430
Good catch, Dante.
I went back and updated the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,439
Messages
5,486,905
Members
407,570
Latest member
cannotquitexcel

This Week's Hot Topics

Top