lookup for last value in a row

[driller]

Hello again,

I forget the formula to find tha last value in a row

A B C D E F=RESULT
AA N1 N2 N2
BB N3 N13 N13
CC N01 N02 N06 N06
DD N8 N11 N11
EE N6 N9 N9

looking for a formula on column F.

thanks in advance.

 

[Mike H]

Maybe

=INDEX(A1:E1,MATCH(REPT("z",20),A1:E1,1)*1)

Mike

 

[Bob Phillips]

=LOOKUP(2,1/(A1:E1<>""),A1:E1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[driller]

thanks a lot.

 

[Bob Phillips]

No need for the *1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[driller]

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again
--
regards,

=LOOKUP(2,1/(A1:E1<>""),A1:E1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

If it is all text

=INDEX($A$1:$E$1,MATCH(REPT("z",20),A2:E2,1))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again

 

[Mike H]

Your right, thank you

No need for the *1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Hi,

It's a bit long winded now you want the date as well

=INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)) &"
["&TEXT(INDEX($A$1:$E$1,MATCH(INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)),A2:E2,FALSE)),"DD/MM/YYYY")&"]"

Mike

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again

 

[driller]

your formula works again. many thanks today().

--
regards,

Hi,

It's a bit long winded now you want the date as well

=INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)) &"
["&TEXT(INDEX($A$1:$E$1,MATCH(INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)),A2:E2,FALSE)),"DD/MM/YYYY")&"]"

Mike

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again

 

[Mike H]

Your welcome and thanks for the feedback

your formula works again. many thanks today().

--
regards,

Hi,

It's a bit long winded now you want the date as well

=INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)) &"
["&TEXT(INDEX($A$1:$E$1,MATCH(INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)),A2:E2,FALSE)),"DD/MM/YYYY")&"]"

Mike

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again
--
regards,



:

=LOOKUP(2,1/(A1:E1<>""),A1:E1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Hello again,

I forget the formula to find tha last value in a row

A B C D E F=RESULT
AA N1 N2 N2
BB N3 N13 N13
CC N01 N02 N06 N06
DD N8 N11 N11
EE N6 N9 N9

looking for a formula on column F.

thanks in advance.

 

[driller]

thanks Sir Bob, i have real dates to be lookup. I use Mike's formula.

--
regards,

Hi,

It's a bit long winded now you want the date as well

=INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)) &"
["&TEXT(INDEX($A$1:$E$1,MATCH(INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)),A2:E2,FALSE)),"DD/MM/YYYY")&"]"

Mike

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again

 

[ryguy7272]

Active Worksheet:
=LOOKUP(10000000000,F:F)

Another Worksheet:
=LOOKUP(10000000000,'Sheet1'!F:F)


Regards,
Ryan---
--
RyGuy

thanks Sir Bob, i have real dates to be lookup. I use Mike's formula.

--
regards,

Hi,

It's a bit long winded now you want the date as well

=INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)) &"
["&TEXT(INDEX($A$1:$E$1,MATCH(INDEX(A2:E2,MATCH(REPT("z",20),A2:E2,1)),A2:E2,FALSE)),"DD/MM/YYYY")&"]"

Mike

my wrong,

i forgot to place that the date shall be in the result as well.
I have a long list to paved by the formula.

A B C D E F=RESULT
DATE 2-Jan-08 14-Jan-08 22-Jan-08 1-Feb-08
AA N1 N2 N2 (22-Jan-08)
BB N3 N13 N13 (22-Jan-08)
CC N01 N02 N06 N06 (1-Feb-08)
DD N8 N11 N11 (22-Jan-08
EE N6 N9 N9 (14-Jan-08)

thanks again
--
regards,



:

=LOOKUP(2,1/(A1:E1<>""),A1:E1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Hello again,

I forget the formula to find tha last value in a row

A B C D E F=RESULT
AA N1 N2 N2
BB N3 N13 N13
CC N01 N02 N06 N06
DD N8 N11 N11
EE N6 N9 N9

looking for a formula on column F.

thanks in advance.