Forum Discussion
dynamic offset with lookup of location
=SUM(INDEX(B2:B8,MATCH(H2,A2:A8,0)):
INDEX(B2:D8,MATCH(H2,A2:A8,0),K2))
Twifoo, What if I did not know where the table of lookup values would be? Rather, I know it'll be on the second row, but perhaps 20 columns to the right. Is there a way to modify the formula to find some starter cell value (assume it's unique), which would mark the upper left corner of the range? So as shown in the revised attached, if I had this setup, and needed one formula, that based on the entry to the right of it, would find the appropriate range, and do the same operation you've expertly instructed in the last post...how could the formula be modified?
Twifoo, any further thoughts that could help me get to this? Basic logic I need is:
1) start at A1, and go right until you find what's in Range Selection
2) from that cell, go down until you find the Row Item
3) in that row, give me the column represented by "Which Answer" selection
I think it's a combination of your index/match functions, set to be one within another, but I don't know how to order them. Hope you have an idea. This would expand the potential of the model into which this would go, exponentially.
Thanks.
- The "SUM" label of the result in H19 is a misnomer because it is really a lookup value, wherein your formula is:
=INDEX((B2:D8,J2:L8),
MATCH(H14,A2:A8,0),
MATCH(H15,B1:D1,0),
MATCH(H16,{"Range 1","Range 2"},0))
