Thanks alot, all formulas worked a treat

"Domenic" wrote:

Assumptions:

A1:F6 contains your data

H1 contains your lookup value, such as 'Name 2'

Formulas:

If your data contains text values, and blanks are actually blank cells
not 'formula' blanks, try...

Last:

=LOOKUP(REPT("z",255),INDEX(B1:F6,MATCH(H1,A1:A6,0 ),0))

Second to last:

=LOOKUP(REPT("z",255),INDEX(B1:F6,MATCH(H1,A1:A6,0 ),1):INDEX(B1:F6,MATCH(
H1,A1:A6,0),MATCH(REPT("z",255),INDEX(B1:F6,MATCH( H1,A1:A6,0),0))-1))

If your data contains text values, and blanks are actually 'formula'
blanks, try...

Last:

=LOOKUP(2,1/(INDEX(B1:F6,MATCH(H1,A1:A6,0),0)<""),INDEX(B1:F6 ,MATCH(H1,A
1:A6,0),0))

Second to last:

First, define the following reference...

Insert Name Define

Name: LPos

Refers to:

=MATCH(2,1/(INDEX(Sheet1!$B$1:$F$6,MATCH(Sheet1!$H$1,Sheet1!$ A$1:$A$6,0),
0)<""))

Click Ok

Then, try the following formula...

=LOOKUP(2,1/((INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B1:F6,MAT CH(H1,A1:A6
,0),LPos-1))<""),(INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B 1:F6,MATCH(H1,
A1:A6,0),LPos-1)))

If your data contains numerical values, try...

Last:

=LOOKUP(9.99999999999999E+307,INDEX(B1:F6,MATCH(H1 ,A1:A6,0),0))

Second to last:

=LOOKUP(9.99999999999999E+307,INDEX(B1:F6,MATCH(H1 ,A1:A6,0),1):INDEX(B1:F
6,MATCH(H1,A1:A6,0),MATCH(9.99999999999999E+307,IN DEX(B1:F6,MATCH(H1,A1:A
6,0),0))-1))

Hope this helps!

In article ,
"Kwanjangnim" wrote:

hi domenic,
i didn't understand your answer, but i've setup an example of what i'm
trying to do

A B C D E f g h
i
1 name 1 z zz zzz zzzz zzzzz
2 name 2 y yy yyy yyyy yyyyy
3 name 3 g gg ggg gggg ggggg
4 name 4 h hh hhh hhhh hhhhh
5 name 5 i ii iii iiii iiiii
6 name 6 f ff fff ffff fffff

i want to lookup a name and return the value of the last entered data within
that row, this lookup is done on a another wooksheet within the wookbook
after that in an adjacent cell i would like to return the next to last value
- yes there will be blank cells within each row. e.g look up 'name 2' and
return last value = 'yyyyy' and next to last 'yyyy' hope thats clear and
thanks for your help so far