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

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

hi, thanks for the reponse, saw previous similar question using word fred,
got got a response, :) this was my nth posting.. last occurance of fred is
the idea.
other info is:

hi, trying to find way to locate text "up" from current position. not sure
which function / how to use. have been looking at: MATCH, LOOKUP.. thanks

example have for find last number in a column is:
=P54=LOOKUP(9.99999999999999E+307,P$24:P$59)

-----

example using for finding next instance "down" from current position is:
MATCH(TRUE,OFFSET($AT427:$AT$1231,1,0)="dn",0)

array portion: guessing would be able to search from fixed top position of
document, down to current position. e.g.: $AT$128:$AT427,-1

***
will be using range before "current" cell/ position to find the letters: "dn"


"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

just looking for fred, not different column data, is that what you have?


"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

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

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

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

$Z$3 is the amount of rows down, for the offset.

hi, example working with, that seems to be correct for what trying to do, is:

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))

placed in row 141, that has a "dn", is not included,
and result: 29 is correct (prev row with "dn")

trying to place in formula, does not seem to take / allow to enter:
purpose of formula is for shortcut up to next text occurance of "dn".

=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")

Sorry, but I have no idea what you're trying to do with this.

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, example working with, that seems to be correct for what trying to do,
is:

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))

placed in row 141, that has a "dn", is not included,
and result: 29 is correct (prev row with "dn")

trying to place in formula, does not seem to take / allow to enter:
purpose of formula is for shortcut up to next text occurance of "dn".

=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")