#1
|
|||
|
|||
Backwards Searching Using a Formula
Hi
I am seaerching for a formula that will reverse search a single column array for the first non blank cell and return the number in the cell to the right of that value. For example: A10 contains "Object Number:", B10 contains "2456" Current Active Cell is A20 Search from A20 backwards to A10 and then when it finds the cell is non-blank then return the value in B10. Any help would be appreciated. Tony |
#2
|
||||
|
||||
Hi Tony,
To find the row with the last alphanumeric value in column A, you could use: =MAX(IF(ISERROR(MATCH("*",A:A,-1)),0,MATCH("*",A:A,-1)),IF(ISERROR(MATCH(1E+306,A:A,1)),0,MATCH(1E+306 ,A:A,1))) So, to find the row with the last last alphanumeric value in column A before the current row, you could use: MAX(IF(ISERROR(MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1)),0,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1)),IF(ISERROR(MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1)),0,MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1))) and to find the corresponding value in column B, you could use: =INDEX(B:B,MAX(IF(ISERROR(MATCH("*",A1:OFFSET(A1,R OW()-1,0),-1)),0,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1)),IF(ISERROR(MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1)),0,MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1)))) The formulae could be made much simpler if the tests were for either numbers or strings, rather than considering both.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul
Many thanks for your prompt response. The search value will always be a string and the returning value will always be a number. Thanks Tony |
#4
|
||||
|
||||
In that case:
=INDEX(B:B,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hi Paul
Your formula finds the first cell that is non blank as I wanted but how do I return the number immediately to the right of that cell? Thanks Tony |
#6
|
|||
|
|||
Quote:
My mistake. It works perfectly. Many thanks for your help. Tony |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
effective searching | mar999 | Office | 1 | 05-04-2011 04:51 AM |
Searching dates to create lists | brianh | Excel | 2 | 01-18-2010 01:58 PM |
Searching Outlook folders not working | cltaylor | Outlook | 0 | 08-31-2009 10:28 AM |
Searching another persons contacts | Hendo313 | Outlook | 1 | 07-10-2009 09:03 AM |
Visio 2002 backwards compatible with 2000 | franklyorange | Visio | 0 | 11-17-2008 11:00 AM |