Lets define

*BigNum* as referring to:

9.99999999999999E+307

That settled...

MATCH(BigNum,X)

where

*X* is either a definite range or a whole column/row reference.

[1] If one wants to invoke the foregoing in order to define a dynamic range,

*X* must be numeric.

*Dynam*, which you mention in your original post and which is defined as referring to

=OFFSET(Sheet7!$C$36,0,0,MATCH(9.9999E+307,Sheet7!$C:$C)-1)

should be formulated a bit differently, assuming that the range in C is numeric:

Define

*Size* as

=MATCH(BigNum,Sheet7!$C:$C)-ROW(Sheet7!$C$36)+1

Then define

*Dynam* as:

=OFFSET(Sheet7!$C$36,0,0,Size)

[2] What if the range in column C consists of solely text, including "", i.e., formula blanks and there isn't any other range to use which is numeric...

Define

*BigText* as:

=REPT("z",255)

Then define

*Size* as:

=MATCH(BigText,Sheet7!$C:$C)-ROW(Sheet7!$C$36)+1

*Dynam* is than defined as before.

[3] What if the range in column C consists of text and numbers, including "", i.e., formula blanks and there isn't any numeric range to use...

Then defining

*Size* as:

Code:

```
=LOOKUP(BigNum,CHOOSE({1,2,3},
MATCH(BigText,Sheet7!$C:$C),
MATCH(BigNum,Sheet7!$C:$C),
MAX(MATCH(BigText,Sheet7!$C:$C),MATCH(BigNum,Sheet7!$C:$C))))-
ROW(Sheet1!$C$36)+1
```

is a good idea.

[4] If the range in column C can contain any value, COUNTA would be an appropriate choice

*if the range does not contain any in-between empty cells*:

The definition of

*Size* changes to:

=COUNTA(Sheet7!$C:$C)-ROW(Sheet7!$C$36)+1

[5] If the range in column C can contain any value and there is at least an in-between empty cell, the definition of

*Size* becomes a bit harder and expensive...

Code:

```
=IF(1-ISBLANK(INDEX(Sheet7!$C:$C,ROWS(Sheet7!$C:$C))),
ROWS(Sheet7!$C:$C),
MATCH(BigNum,1/(1-ISBLANK(OFFSET(INDEX(Sheet7!$C:$C,1),0,0,
ROWS(Sheet7!$C:$C)-1)))))-ROW(Sheet7!$C$36)+1
```

Note. The idiom

MATCH("*",

*X*,-1)

can be better avoided in this context. It would yield a wrong result with:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>

q</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

a</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="" x:fmla='=""'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

w</TD></TR></TBODY></TABLE>

where the cell between that of "a" and that of "w" is a formula blank, i.e., "".

Got the error ...............

MATCH(9.9999E+307,$A:$A) ....

will give error if there is not a single numeric value in column A,

Following are details,

I am using OFFSET formula for dynamic range ..... where i expect the MATCH(...) should return the row number of last filled cell in that column.

But what i learnt is, the formula

=MATCH(9.9999E+307,$A:$A) ... will return row of last "Number" in column A

Where formula

=MATCH("*",$A:$A,-1) ... will return row of last char in column A

Now if i want to get last row in column A, char/number anything , then i need to use,

= LOOKUP(9.999E+307,CHOOSE({1,2,3},MATCH("*",$A:$A,-1),MATCH(9.9999E+307,$A:$A),MAX(MATCH("*",$A:$A,-1),MATCH(9.9999E+307,$A:$A))))

Hope that clears it more !!!

Thanks,

hsk