Using Index & Match Functions to return cell values plus OFFSET ! 3

[TomYC]

I need to return the contents of a cell in a worksheet. The column number will be static (say, 1, or really, 4) while the row number will be a varying number of rows below a row number in the worksheet, and that reference row number I can obtain with the formula:
=MATCH("UNIT: TOTAL",'current ytd rank new'!$B$1:$B$2500,0)
so for example, in my curren ytd rank new worksheet, this formula returns the value of 2237. This means that the formula:
=INDEX('current ytd rank new'!$A$1:$F$2500,MATCH("UNIT: TOTAL",'current ytd rank new'!$B$1:$B$2500,0),1)
which is based on the first formula, returns me the cell reference whose column is $A and whose row is 2237.
OK, but now I want to reference a row, below (beyond) this row number, and whose contents in col. A will be certain text that I specify (eventually this text will be itself referenced from another worksheet, but for this diagnostic I can stipulate it), say "speed of admission."
So, I have a cell-based starting point, and the text-string contents of the cell to which I want to reference, probably with the OFFSET function. And finally I'll offset over to column 4 and I'll have it.
Can someone advise me about how to OFFSET from a cell reference point a certain number or rows indicated by the number of rows away from the reference cell that certain text contents can be found?
Many thanks--
T.Y.

 

[SkipVought]

hi,

Check out faq68-1331

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Thanks, Skip--
I'm studying these, and hoping to make use of the worksheet function version, as I don't know if some future user will have a problem with vba.
But right away, when I see the COUNTA function, I suspect I'll have a problem, as the worksheet to which I am referring (i.e. 'current ytd rank new') receives a data dump on a monthly basis from an online source, and it is not really a table in the sense that it has numerous empty rows. There are groups of data separated by rows of empty cells. I can identify, with my existing INDEX/MATCH functions, the beginning of the section of this worksheet that I would like to use as a table, but it does not get to me as a table!

 

[Gavona]

Can't you simply use the Index Function?
To offset 7 rows add 7 to the result of match. Similarly you can adjust the column using the last parameter.

=INDEX('current ytd rank new'!$A$1:$F$2500,MATCH("UNIT: TOTAL",'current ytd rank new'!$B$1:$B$2500,0)[red]+7[/red],[blue]1[/blue])

Gavin

 

[TomYC]

Thanks, Gavin--
Would that it be always 7!!
In my case, to determine how many rows to offset, I need to determine how many rows below my starting point (cell A2237 in my example above) a certain text string occurs. So when I paste your example into my spreadsheet, it returns "Courtesy of person admitting" but if next month there were more rows between my starting place and the row where that text string is found, the offset of 7 will return me a different value. So what I need is a dynamic function to determine where a text string occurs in a column of values...
T.Y.

 

[Gavona]

OK then, I have a rather convoluted solution for you

I have used named ranges. Your values are:
MyCol = 'current ytd rank new'!$B$1:$B$2500
myMatch1 = "UNIT: TOTAL"
myMatch2 = ?????

This will return the row number within MyRange from which you want to get your result:

[blue]=MATCH(myMatch2,OFFSET(MyCol,MATCH(myMatch1,MyCol,0),0),0)+MATCH(myMatch1,MyCol,0)[/blue]

So the formula you want to return the value from the column [red]2[/red] to the right of MyCol is:
=OFFSET(MyCol,[blue]MATCH(myMatch2,OFFSET(MyCol,MATCH(myMatch1,MyCol,0),0),0)+MATCH(myMatch1,MyCol,0)[/blue]-1,[red]2[/red],1,1)



Gavin

 

[lionelhill]

If you could possibly append the first thing you look for to the second (in a spare, possibly hidden, column), then you would have a column of data where you can look for the first search term appended to the second, with no danger of ever meeting a duplicate. This would turn the problem into a single lookup (or match) with no need for offsets??

 

[SkipVought]

You do not really need the number of rows.

All you need to know is, 1) what COLUMN is the lookup value in and 2) what the table COLUMN range..

Knowing those 2 things, you can return any value in the table COLUMN range, using INDEX & MATCH.

but now I want to reference a row, below (beyond) this row number...

[tt]
=INDEX('current ytd rank new'!$A:$F,MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

I want to thank everyone but Gavona and Skip in particular.
In the end, after wrestling with suggestions, I came up with this:
=OFFSET(INDEX('current ytd rank new'!$A$1:$F$2500,MATCH("UNIT: TOTAL",'current ytd rank new'!$B$1:$B$2500,0),1),MATCH($A2,ALLQUEST!$A$1:$A$122,0),3)
And it works!
I had to create one new worksheet "ALLQUEST" that contains a single column of fixed entries (standard questions, basically) and then I used OFFSET to look for a row below my referenced starting point that contains the text question (here it is cell A2).
Now it's time for lunch! But this will obviate a lot of monthly massaging of data rows, etc., as it should--
T.Y.

 

[TomYC]

Perhaps I spoke too soon?
It turns out that my result was not generalizable.
That is, when the values in the single column list I created as a separate worksheet (ALLQUEST) did not match those in the worksheets from which I was referencing them, I did not get the results I need.
So this question is still out there:
in a worksheet whose contents are replaced monthly, I need to get the values of one or more cells whose "distance" or really offset, from a reference cell location, may vary. I have this formula to give me the "reference" cell or starting point for the search for the desired cells/values:
=INDEX('current ytd rank new'!$A:$F,MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0),1)
This formula gives me the value of what I am calling the starting point. I can even get a text string that indicates is location with this:
=ADDRESS(MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0),1).
OK, so what then?
A certain number of rows below this starting cell location, is a cell whose value is equal to the value on my destination data sheet, in a column of text values (they are questions, actually). If my destination reference cell is $A4 and its value is "speed of admission" then I am looking for the cell in column A on my data source sheet ('current ytd rank new') that contains the same text value, but which is offset somewhere below my reference cell, whose location is given by the formulas I've written above here (the INDEX and ADDRESS ones). It sounds simple enough to me: find the cell whose value equals another cell, counting down from a calculated location!
One thing that might help me here: my ADDRESS formula gives me a $A$1-type result, but I cannot use that result in any subsequent calculation specifying a range, i.e. it stays as text, apparently. If someone can help restate this problem in a more general way, I will search further! Thanks,
T.Y.

 

[GlennUK]

To get the position of the first "speed of admission" in 'current ytd rank new', after "UNIT: TOTAL", it'd be:

=MATCH("speed of admission",INDEX('current ytd rank new'!$A:$A,MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)):'current ytd rank new'!$A$65536,0)+MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)-1

What do you want to do once you've found it?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

when the values in the single column list I created as a separate worksheet (ALLQUEST) did not match those in the worksheets from which I was referencing them, I did not get the results I need.

if MATCH returns an N/A error, then what do you want to happen?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

GlennUK is a StarMan today!
I'm still trying to figure out how he gets MATCH to handle that combination, but I will! In the meantime it works, and all I wanted to do was grab a value a few columns over. So:
=INDEX('current ytd rank new'!$A:$F,MATCH($A4,INDEX('current ytd rank new'!$A:$A,MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)):'current ytd rank new'!$A$65536,0)+MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)-1,4)
gives me the values I need.
Thank you very much--what a way to end the week!
T.Y.

 

[TomYC]

Glenn--
What is the function of
+MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)
in the expression you suggested?
I ask because I think I want to limit my search range so that, if the function doesn't find the targe text in the block of a hundred or so rows that comprise one particular unit (set of questions, actually), it won't go on and find that text in the next set of unit rows, i.e. too far afield!!
What this really means is that ideally I'd like to be searching in between a cell that contains the textstring "Unit:" and the very next cell containing that textstring, about a hundred (but varying) rows further down. Whew!

 

[SkipVought]

Seems like this thread is going in circles. Didn't you start out with wanting to know how to limit the range?

Perhaps it depends on the structure of your data.

You can use the OFFSET function to return a chunk defines by aggregations within the table. But you have this awful data import you are dealing with with empty rows. So you very well might need to do something in VBA, unless you can RESORT the data and slap it into the kind of structure that WILL work as advertised. Your explanation so far has been a bit sketchy.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

What is the function of
+MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)

MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)
Gives the row number within column B that "UNIT: TOTAL" first appears in.

It is first used as the starting point for finding a match for the value in A3.
This returns the number of rows after the starting point in which the match is found.

To find the absolute row number in which the match was found we have to add the row number of the starting point, hence:
+MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0)

It is almost certainly possible to do what you want with a formula for the first block of Unit rows. However rather than a single formula I would store some results ( like MATCH("UNIT: TOTAL",'current ytd rank new'!$B:$B,0) )in separate cells to aid comprehension and de-bugging.

Once this is solved will you want to repeat the exercise for the next set of Unit rows? If so then how many units?
Is a VBA solution acceptable? It would probably be cleaner and might be the only way.

Gavin

 

[Gavona]

Your explanation so far has been a bit sketchy.

Based on my interpretation of your requirementI have a working solution for you. I find it easier to use named ranges and to break a complex formula into bits with the results in different cells. So I am going to post detail here to enable you to construct a workbook containing my exemplification of your data and my solution.

First open a new workbook and name the sheets "Data" and "Results"

Now Copy this and paste into CellA1 of the Data sheet
[Code DataSheet]ColA|ColB|ColC
||Data on Row: 2
||
||Data on Row: 4
||Data on Row: 5
Speed of Admission||Data on Row: 6
||Data on Row: 7
||Data on Row: 8
||Data on Row: 9
||Data on Row: 10
||Data on Row: 11
||Data on Row: 12
||
||
|Unit: Total|Data on Row: 15
||Data on Row: 16
||Data on Row: 17
||Data on Row: 18
||Data on Row: 19
||Data on Row: 20
||Data on Row: 21
||Data on Row: 22
||Data on Row: 23
|Unit: Total|Data on Row: 24
||Data on Row: 25
||Data on Row: 26
||Data on Row: 27
||Data on Row: 28
||
||
||
||Data on Row: 32
||Data on Row: 33
||Data on Row: 34
||Data on Row: 35
||Data on Row: 36
||Data on Row: 37
||Data on Row: 38
||
||Data on Row: 40
Speed of Admission||Data on Row: 41
||Data on Row: 42
||Data on Row: 43
||Data on Row: 44
||Data on Row: 45
||Data on Row: 46
||Data on Row: 47
||Data on Row: 48
||
||Data on Row: 50
||Data on Row: 51
||Data on Row: 52
||Data on Row: 53
|Unit: Total|Data on Row: 54
||Data on Row: 55
||Data on Row: 56
||Data on Row: 57
||Data on Row: 58
||Data on Row: 59
||Data on Row: 60
||
||Data on Row: 62
||Data on Row: 63
||Data on Row: 64
||Data on Row: 65
||Data on Row: 66
||Data on Row: 67
||Data on Row: 68
||Data on Row: 69
Speed of Admission||Data on Row: 70
||Data on Row: 71
||Data on Row: 72
||Data on Row: 73
||Data on Row: 74
||
||Data on Row: 76
||Data on Row: 77
||Data on Row: 78
||Data on Row: 79
||
||Data on Row: 81
|Unit: Total|Data on Row: 82
||Data on Row: 83
||Data on Row: 84
||
||Data on Row: 86
||Data on Row: 87
||Data on Row: 88
||Data on Row: 89
||Data on Row: 90
||Data on Row: 91
||Data on Row: 92
||
Speed of Admission||Data on Row: 94
||Data on Row: 95
||Data on Row: 96
||Data on Row: 97
||Data on Row: 98
||Data on Row: 99
||Data on Row: 100
||Data on Row: 101
||Data on Row: 102
||
||Data on Row: 104
||Data on Row: 105
||Data on Row: 106
|Unit: Total|Data on Row: 107
||Data on Row: 108
||Data on Row: 109
||Data on Row: 110
||Data on Row: 111
||Data on Row: 112
||Data on Row: 113
||Data on Row: 114
||Data on Row: 115
||Data on Row: 116
||Data on Row: 117
||Data on Row: 118
||Data on Row: 119
||Data on Row: 120
||Data on Row: 121
||Data on Row: 122
||Data on Row: 123
||
||Data on Row: 125
||Data on Row: 126
||Data on Row: 127
||Data on Row: 128
||Data on Row: 129
||Data on Row: 130
||Data on Row: 131
||Data on Row: 132
||Data on Row: 133
||Data on Row: 134
||Data on Row: 135
||Data on Row: 136
Speed of Admission||Data on Row: 137
|Unit: Total|Data on Row: 138
[/code]
and this into Cell A1 of the Results sheet[Code ResultsSheet]RangeName|Value/Formula||
ResultFromColumn|=COLUMN(Data!C1)||
MaxRow|65535||
Search1Column|=COLUMN(Data!B1)||
Search2Column|=COLUMN(Data!A2)||
Search1Value|Unit: Total||
Search2Value|Speed of Admission||
|||
|Row number|Search2RowNumber|ReturnValue
LastRowUnit0|0||
LastRowUnit1|=MATCH(Search1Value,OFFSET(Data!$A$1,B10,Search1Column-1,MaxRow-B10,1),0)+B10|=MATCH(Search2Value,OFFSET(Data!$A$1,LastRowUnit0,Search2Column-1,LastRowUnit1,1),0)+C10|=OFFSET(Data!$A$1,Results!C11-1,ResultFromColumn-1)
LastRowUnit2|=MATCH(Search1Value,OFFSET(Data!$A$1,B11,Search1Column-1,MaxRow-B11,1),0)+B11|=MATCH(Search2Value,OFFSET(Data!$A$1,B11,Search2Column-1,B12,1),0)+B11|=OFFSET(Data!$A$1,Results!C12-1,ResultFromColumn-1)
LastRowUnit3|=MATCH(Search1Value,OFFSET(Data!$A$1,B12,Search1Column-1,MaxRow-B12,1),0)+B12|=MATCH(Search2Value,OFFSET(Data!$A$1,B12,Search2Column-1,B13,1),0)+B12|=OFFSET(Data!$A$1,Results!C13-1,ResultFromColumn-1)
LastRowUnit4|=MATCH(Search1Value,OFFSET(Data!$A$1,B13,Search1Column-1,MaxRow-B13,1),0)+B13|=MATCH(Search2Value,OFFSET(Data!$A$1,B13,Search2Column-1,B14,1),0)+B13|=OFFSET(Data!$A$1,Results!C14-1,ResultFromColumn-1)
LastRowUnit5|=MATCH(Search1Value,OFFSET(Data!$A$1,B14,Search1Column-1,MaxRow-B14,1),0)+B14|=MATCH(Search2Value,OFFSET(Data!$A$1,B14,Search2Column-1,B15,1),0)+B14|=OFFSET(Data!$A$1,Results!C15-1,ResultFromColumn-1)
LastRowUnit6|=MATCH(Search1Value,OFFSET(Data!$A$1,B15,Search1Column-1,MaxRow-B15,1),0)+B15|=MATCH(Search2Value,OFFSET(Data!$A$1,B15,Search2Column-1,B16,1),0)+B15|=OFFSET(Data!$A$1,Results!C16-1,ResultFromColumn-1)
LastRowUnit7|=MATCH(Search1Value,OFFSET(Data!$A$1,B16,Search1Column-1,MaxRow-B16,1),0)+B16|=MATCH(Search2Value,OFFSET(Data!$A$1,B16,Search2Column-1,B17,1),0)+B16|=OFFSET(Data!$A$1,Results!C17-1,ResultFromColumn-1)
LastRowUnit8|=MATCH(Search1Value,OFFSET(Data!$A$1,B17,Search1Column-1,MaxRow-B17,1),0)+B17|=MATCH(Search2Value,OFFSET(Data!$A$1,B17,Search2Column-1,B18,1),0)+B17|=OFFSET(Data!$A$1,Results!C18-1,ResultFromColumn-1)
LastRowUnit9|=MATCH(Search1Value,OFFSET(Data!$A$1,B18,Search1Column-1,MaxRow-B18,1),0)+B18|=MATCH(Search2Value,OFFSET(Data!$A$1,B18,Search2Column-1,B19,1),0)+B18|=OFFSET(Data!$A$1,Results!C19-1,ResultFromColumn-1)
LastRowUnit10|=MATCH(Search1Value,OFFSET(Data!$A$1,B19,Search1Column-1,MaxRow-B19,1),0)+B19|=MATCH(Search2Value,OFFSET(Data!$A$1,B19,Search2Column-1,B20,1),0)+B19|=OFFSET(Data!$A$1,Results!C20-1,ResultFromColumn-1)
LastRowUnit11|=MATCH(Search1Value,OFFSET(Data!$A$1,B20,Search1Column-1,MaxRow-B20,1),0)+B20|=MATCH(Search2Value,OFFSET(Data!$A$1,B20,Search2Column-1,B21,1),0)+B20|=OFFSET(Data!$A$1,Results!C21-1,ResultFromColumn-1)
LastRowUnit12|=MATCH(Search1Value,OFFSET(Data!$A$1,B21,Search1Column-1,MaxRow-B21,1),0)+B21|=MATCH(Search2Value,OFFSET(Data!$A$1,B21,Search2Column-1,B22,1),0)+B21|=OFFSET(Data!$A$1,Results!C22-1,ResultFromColumn-1)
LastRowUnit13|=MATCH(Search1Value,OFFSET(Data!$A$1,B22,Search1Column-1,MaxRow-B22,1),0)+B22|=MATCH(Search2Value,OFFSET(Data!$A$1,B22,Search2Column-1,B23,1),0)+B22|=OFFSET(Data!$A$1,Results!C23-1,ResultFromColumn-1)
LastRowUnit14|=MATCH(Search1Value,OFFSET(Data!$A$1,B23,Search1Column-1,MaxRow-B23,1),0)+B23|=MATCH(Search2Value,OFFSET(Data!$A$1,B23,Search2Column-1,B24,1),0)+B23|=OFFSET(Data!$A$1,Results!C24-1,ResultFromColumn-1)
LastRowUnit15|=MATCH(Search1Value,OFFSET(Data!$A$1,B24,Search1Column-1,MaxRow-B24,1),0)+B24|=MATCH(Search2Value,OFFSET(Data!$A$1,B24,Search2Column-1,B25,1),0)+B24|=OFFSET(Data!$A$1,Results!C25-1,ResultFromColumn-1)
LastRowUnit16|=MATCH(Search1Value,OFFSET(Data!$A$1,B25,Search1Column-1,MaxRow-B25,1),0)+B25|=MATCH(Search2Value,OFFSET(Data!$A$1,B25,Search2Column-1,B26,1),0)+B25|=OFFSET(Data!$A$1,Results!C26-1,ResultFromColumn-1)
LastRowUnit17|=MATCH(Search1Value,OFFSET(Data!$A$1,B26,Search1Column-1,MaxRow-B26,1),0)+B26|=MATCH(Search2Value,OFFSET(Data!$A$1,B26,Search2Column-1,B27,1),0)+B26|=OFFSET(Data!$A$1,Results!C27-1,ResultFromColumn-1)
LastRowUnit18|=MATCH(Search1Value,OFFSET(Data!$A$1,B27,Search1Column-1,MaxRow-B27,1),0)+B27|=MATCH(Search2Value,OFFSET(Data!$A$1,B27,Search2Column-1,B28,1),0)+B27|=OFFSET(Data!$A$1,Results!C28-1,ResultFromColumn-1)
LastRowUnit19|=MATCH(Search1Value,OFFSET(Data!$A$1,B28,Search1Column-1,MaxRow-B28,1),0)+B28|=MATCH(Search2Value,OFFSET(Data!$A$1,B28,Search2Column-1,B29,1),0)+B28|=OFFSET(Data!$A$1,Results!C29-1,ResultFromColumn-1)
LastRowUnit20|=MATCH(Search1Value,OFFSET(Data!$A$1,B29,Search1Column-1,MaxRow-B29,1),0)+B29|=MATCH(Search2Value,OFFSET(Data!$A$1,B29,Search2Column-1,B30,1),0)+B29|=OFFSET(Data!$A$1,Results!C30-1,ResultFromColumn-1)
LastRowUnit21|=MATCH(Search1Value,OFFSET(Data!$A$1,B30,Search1Column-1,MaxRow-B30,1),0)+B30|=MATCH(Search2Value,OFFSET(Data!$A$1,B30,Search2Column-1,B31,1),0)+B30|=OFFSET(Data!$A$1,Results!C31-1,ResultFromColumn-1)
LastRowUnit22|=MATCH(Search1Value,OFFSET(Data!$A$1,B31,Search1Column-1,MaxRow-B31,1),0)+B31|=MATCH(Search2Value,OFFSET(Data!$A$1,B31,Search2Column-1,B32,1),0)+B31|=OFFSET(Data!$A$1,Results!C32-1,ResultFromColumn-1)
LastRowUnit23|=MATCH(Search1Value,OFFSET(Data!$A$1,B32,Search1Column-1,MaxRow-B32,1),0)+B32|=MATCH(Search2Value,OFFSET(Data!$A$1,B32,Search2Column-1,B33,1),0)+B32|=OFFSET(Data!$A$1,Results!C33-1,ResultFromColumn-1)
LastRowUnit24|=MATCH(Search1Value,OFFSET(Data!$A$1,B33,Search1Column-1,MaxRow-B33,1),0)+B33|=MATCH(Search2Value,OFFSET(Data!$A$1,B33,Search2Column-1,B34,1),0)+B33|=OFFSET(Data!$A$1,Results!C34-1,ResultFromColumn-1)
[/code]

Now, on each sheet in turn, use Data,TextToColumns, Delimited with a separator of | (bottom left of my UK keyboard or you can copy a separator from the data).

Now we need to tweak the results sheet:
1.Select A1:B34
2.Insert,Name,Create
(creates named ranges for the cells in column B)
3.Convert the text in column B,C and D to formulae

Does that help?

Gavin

 

[Gavona]

For Step 3 you could use this code. Select B1 to D34 then run it.

Sub ConvertBackToFormula()
Dim c As Range
For Each c In Selection
    c.Formula = c.Value
Next c
End Sub

For completeness I converted from formulae to text using this:

Sub ConvertFormulaToText()
Dim c As Range
For Each c In Selection
    c.Value = "'" & c.Formula
Next c
End Sub

and used this formula to create a concatenation of the different columns which I pasted earlier.

=A1&"|"&B1&"|"&C1&"|"&D1



Gavin

 

[Gavona]

Forgot to say, that in the results sheet on row 11 I constructed the formulae using the named ranges. This form is easy to follow but does not copy down so in row 12 I used the same formula but with some of the named ranges replaced with cell references. These formulae simply copy down the worksheet.

Gavin

 

[TomYC]

Thank you Gavin--
Of course I'm still struggling to realize your vision, which I take to lead to a general solution. In the short term, what I have on the "data" side are batches of cells, and each batch begins with a certain Unit name (thus my example was the Unit: Total, beginning with row 2237, but there are many Units above this summary, with names such as ABC and XYZ), and continuing for 120 rows or so (unfortunately a varying number. But solution I have thus far would work if I could limit the upper range of the number of rows to 120, as opposed to the generic 65536. That way, if an item/question doesn't exist within the next 120 rows after the beginning of that Unit's data segment, the function won't go far afield and retrieve the value for the next time it does appear, even if that corresponds to a different Unit!
This must all be frustrating to systemtac thinkers everywhere; however it is one of the real-world data source issues. And as always, I am trying to devise a solution that a future operator can use even in the absence of understanding...