Lookup X-th value that matches criteria

Hi all,

I'm looking for a formula that looks up the 'X'th (e.g. 3th) value that matches a criteria. This is the data:

PHP Code: 

Failure date    Location    departure date
03-01-15    London    ?????????
03-01-15    Paris    ?????????
05-01-15    London    ?????????
10-01-15    London    ?????????
14-01-15    London    ?????????
14-01-15    Paris    ?????????
27-01-15    Paris    ????????? 


Failures occur at multiple locations but flying to each location is expensive. So we only fly when multiple failures have occured, this is the ''batch threshold''. In this example the batch threshold for London is ''3''. So at 03-01-15 the first failure occurs, but the departure date should be the failure date of the third London failure. So in column C (departure date) it should return the date '10-01-15'. If the batch threshold was ''4'' then it would be '14-01-15'. Is it possible to put this into a formula?

Is this array formula what you wanted:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF($F3=$B$2:$B$8,ROW($B$2:$B$8)-ROW($A$2)+1),$G$2)),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

with countif (aantal.als) and a filter on the 3th value.

see the attached file.

... or

=IF(COUNTIF($B$2:$B2,$B2)=$I$2,COUNTIF($B$2:$B2,$B2),"")

where I2 = Batch threshold. In this case there is no need to apply any filter.

So the cell reference from the top must be variable

So are you saying that in your example, (using Glen's sheet for reference), in C2 = 1/10/2015 (m/d/yyyy), C4 will be 1/14/2015 but there will be nothing in C5 or C6 because there are not 3 failed flights from that date?

I don't know that you can do it without arrays without a lot of manipulation that might slow you down just as much.