Condition formulas from text list to table
Results 1 to 6 of 6

Thread: Condition formulas from text list to table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Condition formulas from text list to table

    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.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,384
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Condition formulas from text list to table

    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 by Joe4; Jun 20th, 2019 at 01:02 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Condition formulas from text list to table

    Just another way:

    Enter this formula in CF

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

    Applies to:

    =$M$2:$AP$500
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Condition formulas from text list to table

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

    thank you.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Condition formulas from text list to table

    It also reviews the absolute reference:
    $AQ$430
    Regards Dante Amor

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,384
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Condition formulas from text list to table

    It also reviews the absolute reference:
    $AQ$430
    Good catch, Dante.
    I went back and updated the formula.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •