Forum Discussion
dynamic offset with lookup of location
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))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.
- 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)
