Thread: [Solved] Backwards Searching Using a Formula
View Single Post
 
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: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote