Forum Discussion
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 my formula, however, the output is not as it needs to be.
Can anyone review my formulas and identify why and how I can get it to list only the desired output. I think the issue is with the nested condition in the formulas but I'm not sure what to do.
Hello Darnelle
See attached file. With a few helper cells - to reduce the complexity of the formula.
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
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.
