dynamic offset with lookup of location

[txrussianguy]

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?

[#2]

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

[#3]

@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?

[#4]

@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.

[#5]

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

[#6]

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

1) if I were to add 20 similar ranges, of varying lengths, to the right of these two, would this work?  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.  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.  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.

Thanks for the continued support. Glad that at least one version works.  This was an extract of the many ranges I have, so hopefully it is dynamic enough to handle those. Will await your response.

[#7]

The 3 MATCH functions return, the row, column, and area, respectively. Instead of hard-coding the ranges, you can enter them in F2:F21 for the 20 ranges, then the 3rd MATCH will look like this:
MATCH(H16,F2:F21,0)

[#8]

@Twifoo, I think I've not done a good job at explaining the need.  See attached.  K14 has what I think you're saying the formula needs to be.  H16 I've tried to set to be limited to the CustomerList range (though unsuccessfully), which should be a named range that I can change to expand around the list of varying customer/target names.  The order of the sections of assumptions (up top) will not be in any particular order.  I will simply ensure that the upper left corner of any of them, is within the list of customer names (I'll do that with similar validation rules, as H16 (assuming you can help me with that one as well).

With that in mind, how is K14 identifying the item from Range selection, and then using that range as the one within which it goes to find the "Which Answer" and "Row Item" combo that I'm selecting?

Sorry if I'm just not seeing something obvious.  I feel this is just on the cusp of what I need, but not quite getting there.  Let me know your thoughts.

Thanks.

[#9]

You have repeatedly modified the scenario to a point that I now find everything ambiguous. You must ensure that each conversation you start is limited to only 1 scenario with specific results you want to achieve. Any modification to your originally given scenario must instead be posted to a new conversation.

[#10]

@Twifoo, sorry.  I tried putting this idea out originally.  It was not understood.  I tried to break it down into the components.  Obviously didn't quite work, though I think what you gave me comes very close.  I'll try to reframe it in another post.

Thanks again.

[#11]

Welcome!