Formula to bring back value based on criteria in a column plus 4 columns away

    Hoping for some help!


    I trying to write a formula to bring back a value


    essentially it needs to look down column "G" until it finds a specific criteria such as "Total Cases" then bring back the data held in the same row that "Total Cases" appears in column A




    [TABLE="width: 200"]

    [tr]


    [TD="class: xl63, width: 200"][/TD]

    [/tr]


    [/TABLE]

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    index match should do that for you


    =index(A:A, match("total cases", G:G, 0 ))


    if total cases is in a cell and will change , then put the cell reference in
    =index(A:A, match(cell, G:G, 0 ))

    ETAF

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    Quote from etaf;701594

    index match should do that for you


    =index(A:A, match("total cases", G:G, 0 ))


    if total cases is in a cell and will change , then put the cell reference in
    =index(A:A, match(cell, G:G, 0 ))



    Perfect thanks!


    next i need!


    the same as above but to bring back the data in column A than is 1 row below the matched data in column G

    :)

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    =index(A:A, match("total cases", G:G, 0 )+1)

    ETAF

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    Quote from etaf;701598

    =index(A:A, match("total cases", G:G, 0 )+1)



    cool ...


    next i need to add 3 or 4 matches in column g together and then divide them by data in column a which has been found using a different match in column g

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    just use the index/match and add together and divide as normal


    = (index(A:A, match("total cases", G:G, 0 )) + index(A:A, match("total cases", G:G, 0 )) + index(A:A, match("total cases", G:G, 0 )+1) )
    /
    ( i    ndex(A:A, match("total cases", G:G, 0 )+1) + index(A:A, match("total cases", G:G, 0 )+1) )


    obviously the "total cases" needs to change and all on one line - just split to show


    ETAF

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    It gives back the wrong % it should be 169% but i get 11806%


    =(INDEX('Raw Data Nov 13'!A:A,MATCH("Orange",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Mango",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Other",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Red",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Teal",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Black",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Brown",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
    /
    (INDEX('Raw Data Nov 13'!A:A,MATCH("Colours",'Raw Data Nov 13'!G:G,0))))

    Re: Formula to bring back value based on criteria in a column plus 4 columns away


    you need another bracket at the end of all the plus
    excel does / and * first
    so
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
    /
    (INDEX('Raw Data Nov 13'!A:A,MATCH("Colours",'Raw Data Nov 13'!G:G,0))


    will be done first


    you need


    =(
    INDEX('Raw Data Nov 13'!A:A,MATCH("Orange",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Mango",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Other",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Red",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Teal",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Black",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Brown",'Raw Data Nov 13'!G:G,0))
    +INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
    )
    /


    to do the + together


    you may have the formatting of the cell to % and returning a number that may explain a 100 error


    if still an error


    put each formula in to a cell separately and see what result you get for every one
    and then use a calc and check the answer

    ETAF

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login