Prev: why does a sumproduct formula return a #div/0!
Next: existing connections
From: Gibbyky2 on 14 Sep 2009 15:17
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!!!!!!!!!!!!!
From: Pete_UK on 14 Sep 2009 15:29
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 <Gibby...(a)discussions.microsoft.com>
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!!!!!!!!!!!!!

From: Gibbyky2 on 14 Sep 2009 15:47
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 <Gibby...(a)discussions.microsoft.com>
> 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!!!!!!!!!!!!!
>
>
From: Pete_UK on 14 Sep 2009 16:03
Well, did your try those two VLOOKUP formulae? Did they work for you
in different situations?

Pete

On Sep 14, 8:47 pm, Gibbyky2 <Gibby...(a)discussions.microsoft.com>
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 <Gibby...(a)discussions.microsoft.com>
> > 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 -

From: Bernd P on 14 Sep 2009 17:19
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
 |  Next  |  Last
Pages: 1 2
Prev: why does a sumproduct formula return a #div/0!
Next: existing connections