Searching the Substring with the Largest side value

  • I have a list that sort though anime, tags, and my ratings for them. that's on list one, on list two is a genre sorter. it show how many shows fit into one tag. in the column next to that one i want it to show the highest rating of that tag. i've tryed max/if and index/match but i can't seem to get either to work. how would i go about this?
    ex
    Name Tag Rating
    Hellsing Vampire 4
    hellsing ult Vampire 9
    blood lad Vampire 8
    strike the blood Vampire 6
    Blood c Vampire 6

    Files

  • Re: Searching the Substring with the Largest side value


    Try this. :cool:


    Get rid of all those blank table rows and then you can put this formula into C2 of the Genre sheet.


    It's an array formula so after you paste the formula into C2 hit control + shift + enter.


    You should see {} on either side of the formula if you did the control + shift + enter part right.


    Array formula basics.


    You can then copy the formula down the column.


    Code
    1. =MAX(IF(ISNUMBER(FIND(Table3[@Genres],Table2[Tags])),Table2[Rating],0))

    Bruce :cool:

  • Re: Searching the Substring with the Largest side value


    Sorry if this is late or annoying, but can that code be repurposed to find the row number of the tags
    so i have it search just for Military and it returns 5
    or have it search for action (and drap the code drown multipe cells) and return 2,3,4 all in different cells?
    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Tag

    [/td]


    [/tr]


    [tr]


    [td]

    Assination Classroom

    [/td]


    [td]

    [TABLE="width: 293"]

    [tr]


    [td]

    Action, Comedy, School, Shounen

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 328"]

    [tr]


    [td]

    Akame ga Kill!

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    [TABLE="width: 293"]

    [tr]


    [td]

    Action, Adventure, Fantasy

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 328"]

    [tr]


    [td]

    Blade and Soul

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    [TABLE="width: 293"]

    [tr]


    [td]

    Action, Adventure, Fantasy, Martial Arts

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 328"]

    [tr]


    [td]

    Seisen Cerberus

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    [TABLE="width: 293"]

    [tr]


    [td]

    Military

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [/TABLE]