Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a column for last instance of text
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a column for last instance of text
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a column for last instance of text
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(INDI RECT("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) "nastech" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for first instance of text & return the value | Excel Worksheet Functions | |||
URGENT: Search for text in column and count by month | Excel Discussion (Misc queries) | |||
Fast way to search many cells by column for text strings | Excel Discussion (Misc queries) | |||
Search a Column by text length | Excel Worksheet Functions | |||
Search column and move text formula | Excel Discussion (Misc queries) |