hi, thanks.. I got that to work, trying to incorporate that into a hyperlink
formula that is very useful for moving down a document; I was trying to
reverse the idea, to move up a document.

first example works for down, keys on: "dn" that col.
2nd example problem: looking for an UP formula that keys off same "dn", in
that same column. will put up formula in next column to right, same row.
thanks.

1st example, for moving down a document seems to work well, but do you think
it was properly done..? anyways, note: ctrl-shift-enter array / does not
cut paste well (at least if calculation is turned off). working down example
is:

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$123 2,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$ Z$3,1))),"dn")


NOT WORKING UP Example is:
problems might be too many row(offsets ?
note: using start of document rows / use of "$" signs, and -1's in offset

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),-1,1))),"u")

XXXXXXXXX

"T. Valko" wrote:

Ok...

A1...Fred
A2...Biff
A3...Fred
A4...Fred
A5...Lisa

="A"&LOOKUP(2,1/(A1:A5="Fred"),ROW(A1:A5))

Returns: A4

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
looking for cell reference to "last" occurance of text, in 1 column.
e.g.:
"fred"

"T. Valko" wrote:

Do you mean there are multiple instances of "Fred" and you want to find
the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the
corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks