Match relevant data again

    I need to match up population values for various countries, launch years, and age groups that vary across a time span.
    I know that some sort of match/index and/or vlookup and/or hlookup is needed but I can't wrap my head around how these formulas relate to the page geometry.


    Workbook attached.


    If someone could adjust the existing non-working formula for the France, row 11257, I can do the rest.
    The issue is to find the correct year and country, then sum up the population values for the age groups selected and plop that sum into the correct year column, and then onto the next year.


    Any help is greatly appreciated!
    File: forum.ozgrid.com/index.php?attachment/72182/


    Re: Match relevant data again


    Try this:


    =INDEX($G$2:$G$11251,(MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)))


    This needs to be entered as an array formula using CTRL+SHIFT+ENTER (not just ENTER).

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Match relevant data again


    Thank you again!
    I don't immediately grasp this part:
    (MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)
    and if the * is indicating multiplication (I don't think so).
    I have to run out; will review and consider the formula some more.


    Re: Match relevant data again


    Here's how it works. This bit:


    ($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268)


    is indeed a multiplication, but because this is an array formula, it produces an array that would look something like this if it were only 10 rows long:


    {1;1;1;1;0;0;0;0;0;0}*{0;0;0;1;0;0;0;0;0;0}


    When multiplied together, only the row where the match exists (the fourth in this example) will match the lookup value of 1 (the other rows would return 0), so this:


    MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)


    finds the row where both requirements exist. This, in your own file, is the row that matches both D11268 and L11268.


    It works in a similar way to SUMPRODUCT when used to produce the same results that are sometimes reached using COUNTIFS.


    May I suggest that you use the Evaluate Formula function in Excel to step through the formula so that you can see, step-by-step, what it is doing and the results that it is generating along the way?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Match relevant data again


    Let me know if you want any more help or further explanation. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Match relevant data again


    Ali,


    It works but only for a single age. I need to sum up a number of ages, say from 60-80 years of age.


    I'm working on cell L11269 for ages 83 to 84 to keep it simple in this file: [ATTACH=CONFIG]72186[/ATTACH]


    I tried to also use a NamedRange in the formula (for UK: uk_sexes which refers to a helper cell that shows the NamedRange to select the population data from to have it select the sums from the desired column of data, e.g., Both Sexes, Male Only, Female Only, but it did not work:
    =INDEX($G$2:$G$11251,(MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)))
    changed to:
    =INDEX(uk_sexes,(MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)))


    uk_sexes is cell F11268 and the cell value is both_pop as I want the formula to capture pop data for both sexes.
    both_pop is the named range for the Both Sexes Population F2:F11251.


    Additional help is appreciated!


    Re: Match relevant data again


    You have rather shifted the goalposts, Bob! You did not state or indicate this requirement in your original query or attachment. ;)


    Try this:


    =SUMIFS($F$2:$F$11251,$D$2:$D$11251,">="&D11268,$D$2:$D$11251,"<="&E11268,$C$2:$C$11251,L11268)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Match relevant data again


    Mea culpa!
    I apologize for causing you to do needless work on my behalf and do appreciate your most recent assistance.

    Re: Match relevant data again


    No worries! LOL!


    I hope the SUMIFS works - remember, you can add extra criteria to these. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Match relevant data again


    I appreciate it.
    I use sumifs frequently and got hung up on index/match. Forest for the trees.

Participate now!

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

Register Yourself Login