dynamic offset with lookup of location

Question

I need H3 in the attached to take the value selected in H2 and search in column A to find the location of that line...then sum up the number of values in the first X columns (selected in K2).

Any thoughts?

twifoo · Answer

The formula in H3 is:=SUM(INDEX(B2:B8,MATCH(H2,A2:A8,0)):INDEX(B2:D8,MATCH(H2,A2:A8,0),K2))

txrussianguy · Answer

Twifoo, What if I did not know where the table of lookup values would be?&nbsp; Rather, I know it'll be on the second row, but perhaps 20 columns to the right.&nbsp; 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?&nbsp; 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?

txrussianguy · Answer

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.

dynamic offset.xlsx10 KB

twifoo · Answer

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))

txrussianguy · Answer

Twifoo, it seems to be working, but a few questions (for my education, and for understanding what I can and can't do with this in terms of expansion):&nbsp;1) if I were to add 20 similar ranges, of varying lengths, to the right of these two, would this work?&nbsp; If not, what can be done to allow it to?2) is the listing in the last match (where you list the Range 1, Range 2, etc.) possibly to not have hard-coded, but rather just work in a way where it is simply looking across row 1 for a match to the words found in H16, and because it finds it, that's how it knows it's found the right column in which to start?3) just for my understanding, if you could help me understand what the different lines of the formula do, would be useful.&nbsp; Right now when I click into it (and the coloring of cells shows where it is reading), I'm having a tough time understanding why it's highlighting the first row/column of the first area, but only the results of the second area.&nbsp; And again, this may tie somewhat into my concern about if/how it's possible to make this work with many more ranges, placed out to the right of these.&nbsp;Thanks for the continued support. Glad that at least one version works.&nbsp; This was an extract of the many ranges I have, so hopefully it is dynamic enough to handle those. Will await your response.

Forum Discussion

dynamic offset with lookup of location

10 Replies

Resources