Try the below formula with lookup value in cell D1

In cell D1 : P100AB

In cell E1 :
=LOOKUP(10^10,OFFSET($C$1,MATCH(D1,Sheet1!A:A,0)-1,,
IF(ISNA(MATCH(TRUE,INDEX(INDIRECT("Sheet1!A"&
MATCH(D1,Sheet1!A:A,0)+1&":A1000")<"",),)),1000,
MATCH(TRUE,INDEX(INDIRECT("Sheet1!A"&
MATCH(D1,Sheet1!A:A,0)+1&":A1000")<"",),))))

If this post helps click Yes
---------------
Jacob Skaria


"Robbo12" wrote:

Hi
I am trying to format a spread sheet and need to return the value of colunm
C but the problem is Colunm A is blank as it runs in sequence (see below)
It looks like the following

A B C
1, P100AA Info 300
2, P100AB Info 150
3, "Blank" "" 100
4, "Blank" "" 50
5, P100AC Info 300

How do i return the last figure for the item in Row 2 (colunm A) i.e. 50?

If i do a logical test how do i tell it that row 4 is actually relating to
the search criteria in row 2 without returning the value in row 2, colunm C?