Hi there hope someone can help, here goes

Date RM MR

05/08/1997 £13.64 £53.41
04/05/1998 £62.94 £246.44

i am looking for a formula to check which date is the oldest and put in the
corresponding amounts

ie as above 05/08/97 is oldest date therefore A10= £13.64 A11= £53.41 and
B10=£62.94 B11=£246.44

hope this makes sense!!!!!!!!!!!!!

Have you only got two records, or is this a slimmed-down example?

To find the oldest date you can use MIN:

=MIN(A2,A3)

or:

=MIN(range)

if you have more dates.

Then you can use VLOOKUP to retrieve the values that correspond to
that minimum date:

=VLOOKUP(MIN(A$2,A$3),$A$2:$B$3,2,0)

to get the value from column B, and:

=VLOOKUP(MIN(A$2:A$3),$A$2:$C$3,3,0)

to get the value from column C.

Hope this helps.

Pete

On Sep 14, 8:17*pm, Gibbyky2
wrote:
Hi there hope someone can help, here goes

Date * * * * * * * * * * RM * * * * * *MR

05/08/1997 * * *£13.64 £53.41
04/05/1998 * * *£62.94 £246.44

i am looking for a formula to check which date is the oldest and put in the
corresponding amounts

ie as above 05/08/97 is oldest date therefore A10= £13.64 A11= £53.41 and
B10=£62.94 B11=£246.44

hope this makes sense!!!!!!!!!!!!!

HI pete_uk

it would only ever be 2 dates but the values in relation to the dates would
change

"Pete_UK" wrote:

Have you only got two records, or is this a slimmed-down example?

To find the oldest date you can use MIN:

=MIN(A2,A3)

or:

=MIN(range)

if you have more dates.

Then you can use VLOOKUP to retrieve the values that correspond to
that minimum date:

=VLOOKUP(MIN(A$2,A$3),$A$2:$B$3,2,0)

to get the value from column B, and:

=VLOOKUP(MIN(A$2:A$3),$A$2:$C$3,3,0)

to get the value from column C.

Hope this helps.

Pete

On Sep 14, 8:17 pm, Gibbyky2
wrote:
Hi there hope someone can help, here goes

Date RM MR

05/08/1997 £13.64 £53.41
04/05/1998 £62.94 £246.44

i am looking for a formula to check which date is the oldest and put in the
corresponding amounts

ie as above 05/08/97 is oldest date therefore A10= £13.64 A11= £53.41 and
B10=£62.94 B11=£246.44

hope this makes sense!!!!!!!!!!!!!

Well, did your try those two VLOOKUP formulae? Did they work for you
in different situations?

Pete

On Sep 14, 8:47*pm, Gibbyky2
wrote:
HI pete_uk

it would only ever be 2 dates but the values in relation to the dates would
change



"Pete_UK" wrote:
Have you only got two records, or is this a slimmed-down example?

To find the oldest date you can use MIN:

=MIN(A2,A3)

or:

=MIN(range)

if you have more dates.

Then you can use VLOOKUP to retrieve the values that correspond to
that minimum date:

=VLOOKUP(MIN(A$2,A$3),$A$2:$B$3,2,0)

to get the value from column B, and:

=VLOOKUP(MIN(A$2:A$3),$A$2:$C$3,3,0)

to get the value from column C.

Hope this helps.

Pete

On Sep 14, 8:17 pm, Gibbyky2
wrote:
Hi there hope someone can help, here goes

Date * * * * * * * * * * RM * * * * * *MR

05/08/1997 * * *£13.64 £53.41
04/05/1998 * * *£62.94 £246.44

i am looking for a formula to check which date is the oldest and put in the
corresponding amounts

ie as above 05/08/97 is oldest date therefore A10= £13.64 A11= £53.41 and
B10=£62.94 B11=£246.44

hope this makes sense!!!!!!!!!!!!!- Hide quoted text -

- Show quoted text -

Hello,

If I understand you correctly, select A10:B10 and array-enter
=INDEX(B1:B2,1+(A1A2)):INDEX(C1:C2,1+(A1A2))

Then array-enter into A11:B11:
=INDEX(B1:B2,1+(A1<=A2)):INDEX(C1:C2,1+(A1<=A2))

Regards,
Bernd

Why not simply:

A10: =INDEX(B1:B2,1+($A1$A2))
A11: =INDEX(B1:B2,1+($A1<=$A2))

Copied across to B10:B11

:-)

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

If I understand you correctly, select A10:B10 and array-enter
=INDEX(B1:B2,1+(A1A2)):INDEX(C1:C2,1+(A1A2))

Then array-enter into A11:B11:
=INDEX(B1:B2,1+(A1<=A2)):INDEX(C1:C2,1+(A1<=A2))

Regards,
Bernd

Hello Biff,

Why not simply:

A10: =INDEX(B1:B2,1+($A1$A2))
A11: =INDEX(B1:B2,1+($A1<=$A2))

Copied across to B10:B11

:-)


....Yeah, that'll work :-)

Regards,
Bernd