search a column for last instance of text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
Try the following array formula. Change the A1000 to some cell that you know
will always be past the end of the real data.

=OFFSET(A1,MAX((INDIRECT("A1:A100")<>"")*(ROW(INDIRECT("A1:A100"))))-1,0)

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }.

See www.cpearson.com/Excel/ArrayFormulas.aspx for much more detail about
array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top