Microsoft Office Forums Backwards Searching Using a Formula
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-16-2011, 06:37 AM
 OTPM Windows 7 32bit Office 2010 32bit Expert Join Date: Apr 2011 Location: West Midlands Posts: 981
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
05-16-2011, 06:00 PM
 macropod Windows 7 32bit Office 2007 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,760

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
[MS MVP - Word]
#3
05-17-2011, 12:33 AM
 OTPM Windows 7 32bit Office 2010 32bit Expert Join Date: Apr 2011 Location: West Midlands Posts: 981

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
05-17-2011, 01:33 AM
 macropod Windows 7 32bit Office 2007 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,760

In that case:
=INDEX(B:B,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1))
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
#5
05-17-2011, 07:58 AM
 OTPM Windows 7 32bit Office 2010 32bit Expert Join Date: Apr 2011 Location: West Midlands Posts: 981

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
05-17-2011, 09:17 AM
 OTPM Windows 7 32bit Office 2010 32bit Expert Join Date: Apr 2011 Location: West Midlands Posts: 981

Quote:
 Originally Posted by OTPM 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
Hi Paul
My mistake. It works perfectly.
Tony

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post mar999 Office 1 05-04-2011 04:51 AM brianh Excel 2 01-18-2010 01:58 PM cltaylor Outlook 0 08-31-2009 10:28 AM Hendo313 Outlook 1 07-10-2009 09:03 AM franklyorange Visio 0 11-17-2008 11:00 AM

All times are GMT -7. The time now is 06:53 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top