Forum Discussion
Requesting assistance with dynamic list with 2 way lookup
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.
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.
I put an explanation in cell C35.