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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
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,117
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,117
Office Version
2007
Platform
Windows
It also reviews the absolute reference:
$AQ$430
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
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,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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top