Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-16-2011, 06:37 AM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-16-2011, 06:00 PM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,392
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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]
Reply With Quote
  #3  
Old 05-17-2011, 12:33 AM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-17-2011, 01:33 AM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,392
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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

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
Reply With Quote
  #6  
Old 05-17-2011, 09:17 AM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by OTPM View Post
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.
Many thanks for your help.
Tony
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 10:16 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft