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]

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