In VB, there is a way to specify a range to the bottom of filled cells:
.....Range("A65536").End(xlUp).Address
Is there something like that in the worksheet functions?
I have a cell that has this command:
=IF(ISERROR(VLOOKUP($B62,'91
SCTB'!$C$2:$D$570,2,FALSE))=FALSE,VLOOKUP($B62,'91
SCTB'!$C$2:$D$570,2,FALSE),"NO MATCH")
And it's kinda ugly, I know, but I want to change the $D$570 to something
like the above, where it just searches down to the bottom of the filled
cells. The area changes every now and then, and I want it so the search will
work even though the user (me or someone else) forgot to change the range in
the above function.
I guess I could do 65536, but I was wondering if there was something more
cleaner.
Thanks alot.

=IF(NOT(ISERROR(VLOOKUP($B62,INDIRECT("'91
SCTB'!C2:D"&MAX(IF(ISBLANK($D$1:$D$65535),0,ROW($D $1:$D$65535)))),2,FALSE)))
,VLOOKUP($B62,INDIRECT("'91
SCTB'!C2:D"&MAX(IF(ISBLANK($D$1:$D$65535),0,ROW($D $1:$D$65535)))),2,FALSE),"
NO MATCH")

it is an array formuila, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ian Elliott" wrote in message
...
In VB, there is a way to specify a range to the bottom of filled cells:
....Range("A65536").End(xlUp).Address
Is there something like that in the worksheet functions?
I have a cell that has this command:
=IF(ISERROR(VLOOKUP($B62,'91
SCTB'!$C$2:$D$570,2,FALSE))=FALSE,VLOOKUP($B62,'91
SCTB'!$C$2:$D$570,2,FALSE),"NO MATCH")
And it's kinda ugly, I know, but I want to change the $D$570 to something
like the above, where it just searches down to the bottom of the filled
cells. The area changes every now and then, and I want it so the search
will
work even though the user (me or someone else) forgot to change the range
in
the above function.
I guess I could do 65536, but I was wondering if there was something more
cleaner.
Thanks alot.