Forum Discussion

Darnelle Delva's avatar
Darnelle Delva
Copper Contributor
Sep 07, 2018

Requesting assistance with dynamic list with 2 way lookup

I am trying to make a summary table from a larger dataset.  I've attached a fictitious sample of data that I've used to conceptualize my desired output.  I am using index small and match functions in...
excel
Formulas and Functions
office 365
  • Darnelle Delva's avatar
    Darnelle Delva
    Copper Contributor
    Sep 11, 2018

    Wow.  Thank you very much for this response.

     

    Would it be possible for you to explain the aggregate formula that you used

    - particularly the latter part of the formula with the mmult and row 

    ---- why is it row ($1:$12)?

    ---- what does the exponent do?

    ---- what does the -6 at the end refer to?

     

    I provided only a fictitious simplified sample and planned to use the response to my inquiry in my true dataset, which is a far greater set of data with more than 100 rows and with column dates headers ranging from Jan 2015 to present

    - given this information, do you anticipate any changes to your response/formulas?

    ---- at the moment I'm thinking I may need more helper rows and tables since my desired output will be more than the one table with similar conditions

     

     

    Thanks again

    This truly is helpful

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Sep 11, 2018

      Q: Why is it row ($1:$12)?

      A: Press F5, type C35 and ENTER.

       

      Q: What does the exponent do?

      A: Get a blank worksheet. Type in A1 any number. Type the formula =A1^0 in B1. Change the number in A1 several times. n^0=1.

       

      Q: What does the -6 at the end refer to?

      A: Your data starts in row 7 and so do the cell references in the formula. ROW() would return row numbers from 7 to 16. By subtracting 6 the smallest number would 1 - the first row in your data array.

       

      Generally you should use the formula evaluation which steps to every calculation and operation in a formula.

      • Darnelle Delva's avatar
        Darnelle Delva
        Copper Contributor
        Sep 11, 2018

        Thanks again for the reply.

        I am attempting to update the formulas you shared for use with my live data

         

        Unfortunately, I don't understand the first answer that you gave.

        - In the formula, you shared part of the formula had "row($1:$12)",

        ---- Why are these rows selected?

        ---- If I have more rows in my reference table, how does this part of the formula change, if at all?

        ---- When following the prompts you listed (Press F5, type C35 and ENTER) there wasn't any output for me, I'm not actually sure what I was supposed to see.

         

        Thanks again

        I look forward to your reply.

Resources