Lookup/Index 2 Criteria, find the result with furthest date
 

I Hhve the formula below that finds a date in Column I, that is "for sale"
and is in "Suburb"

=INDEX(I:I,MATCH(1,('Banner Sold (A:A="Suburb")*(B:B="For Sale"),0))

The problem I have is sometimes there might be two options that agree with
this, and I need to select the date that is the furthest away.

Any suggestions on how to do this would be great.

Lookup/Index 2 Criteria, find the result with furthest date
 

Try this array formula** :

=MAX(IF(A1:A100="Suburb",IF(B1:B100="For Sale",I1:I100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"KimC" wrote in message
...
I Hhve the formula below that finds a date in Column I, that is "for sale"
and is in "Suburb"

=INDEX(I:I,MATCH(1,('Banner Sold (A:A="Suburb")*(B:B="For Sale"),0))

The problem I have is sometimes there might be two options that agree with
this, and I need to select the date that is the furthest away.

Any suggestions on how to do this would be great.

Lookup/Index 2 Criteria, find the result with furthest date
 

I did try this, and it did work - but not consistantly. This might help to
understand what i need

This is an example of the data i have:

Suburb Section Date
Suburb 1 For Sale 14/07/2009
Suburb 3 For Sale 13/05/2009
Suburb 2 For Sale 14/06/2010
Suburb 1 For Sale 14/03/2010
Suburb 3 For Sale 14/06/2010
Suburb 2 For Sale 13/05/2009
Suburb 1 For Sale 15/12/2010
Suburb 3 For Sale 14/09/2009

and this is the ideal results

For Sale
Suburb 1 15/12/2010
Suburb 2 14/06/2010
Suburb 3 14/06/2010

The section part does need to be taken in consideration as it will be
different at times.

Does this make more sense?

Cheers,
Kimberley

"T. Valko" wrote:

Try this array formula** :

=MAX(IF(A1:A100="Suburb",IF(B1:B100="For Sale",I1:I100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"KimC" wrote in message
...
I Hhve the formula below that finds a date in Column I, that is "for sale"
and is in "Suburb"

=INDEX(I:I,MATCH(1,('Banner Sold (A:A="Suburb")*(B:B="For Sale"),0))

The problem I have is sometimes there might be two options that agree with
this, and I need to select the date that is the furthest away.

Any suggestions on how to do this would be great.


.

Lookup/Index 2 Criteria, find the result with furthest date
 

Just add another test for the section.

Still array entered:

=MAX(IF(A1:A100="Suburb",IF(B1:B100=1,IF(C1:C100=" For Sale",I1:I100)))

--
Biff
Microsoft Excel MVP


"KimC" wrote in message
...
I did try this, and it did work - but not consistantly. This might help to
understand what i need

This is an example of the data i have:

Suburb Section Date
Suburb 1 For Sale 14/07/2009
Suburb 3 For Sale 13/05/2009
Suburb 2 For Sale 14/06/2010
Suburb 1 For Sale 14/03/2010
Suburb 3 For Sale 14/06/2010
Suburb 2 For Sale 13/05/2009
Suburb 1 For Sale 15/12/2010
Suburb 3 For Sale 14/09/2009

and this is the ideal results

For Sale
Suburb 1 15/12/2010
Suburb 2 14/06/2010
Suburb 3 14/06/2010

The section part does need to be taken in consideration as it will be
different at times.

Does this make more sense?

Cheers,
Kimberley

"T. Valko" wrote:

Try this array formula** :

=MAX(IF(A1:A100="Suburb",IF(B1:B100="For Sale",I1:I100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"KimC" wrote in message
...
I Hhve the formula below that finds a date in Column I, that is "for
sale"
and is in "Suburb"

=INDEX(I:I,MATCH(1,('Banner Sold (A:A="Suburb")*(B:B="For Sale"),0))

The problem I have is sometimes there might be two options that agree
with
this, and I need to select the date that is the furthest away.

Any suggestions on how to do this would be great.


.