Working from oldest date

[Gibbyky2]

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!!!!!!!!!!!!!

 

[Pete_UK]

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

 

[Gibbyky2]

HI pete_uk

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

 

[Pete_UK]

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

Pete

 

[Bernd P]

Hello,

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

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

Regards,
Bernd

 

[T. Valko]

Why not simply:

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

Copied across to B10:B11

 

[Bernd P]

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