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

  • Thread starter Thread starter KimC
  • Start date Start date
K

KimC

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.
 
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.
 
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
 
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)))
 
Back
Top