find row number of all cells that contain substring

  • this is a varation of a different question (http://www.ozgrid.com/forum/sh…php?t=201008&goto=newpost**)
    the table is simlar to the one i have, how do i have it search "Military" and have it returns 5
    or have it search for action (and drop the code though multipe cells) and return 2,3,4?
    i've tryed manipulating "{SMALL(IF(AniList!C:C="Action",ROW(AniList!A:A)),ROW(1:1))} but it only return "2"
    what am i doing wrong?
    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Tag

    [/td]


    [/tr]


    [tr]


    [td]


    Assination Classroom

    [/td]


    [td]

    Action

    [/td]


    [/tr]


    [tr]


    [td]


    Akame ga Kill!

    [/td]


    [td]

    Action, Adventure, Fantasy

    [/td]


    [/tr]


    [tr]


    [td]

    Blade and Soul

    [/td]


    [td]

    Action, Adventure, Fantasy, Martial Arts

    [/td]


    [/tr]


    [tr]


    [td]

    Seisen Cerberus

    [/td]


    [td]

    Military

    [/td]


    [/tr]


    [/TABLE]

  • Re: find row number of all cells that contain substring


    Since "Action" is not the only thing contained in the cell, you need to use SEARCH or a LEFT (if want to say starts with).


    e.g.


    [COLOR="#0000FF"]=SMALL(IF(ISNUMBER(SEARCH("Action",B:B)),ROW(B:B)),ROW(1:1))[/COLOR]


    confirmed with CTRL+SHIFT+ENTER.


    It is recommended by the way, not to use Full column references in array formulas as they will be processor hungry and slow things down.


    You might also wrap an IFERROR() around the formula to return a blank instead of error when no more are found...

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016