Excel 2007 : Multiple criteria without using an array formula

Hi,
I am a new member but relatively accomplished on excel.
However I am having problems with a sheet that currently uses the following array formula to find a date.
My data is arranged as follows:-

PID Range ActSheet range StartDates Range FinishDates Range

Ref Number ident Start date Finish date
4.600 Loc1 21/10/2012 27/10/2012
4.600 Loc1 23/10/2012 28/10/2012
4.600 Loc2 27/10/2012 29/10/2012
4.600 Loc2 27/10/2012 30/10/2012
4.600 Loc2 30/10/2012 31/10/2012
4.600 Loc3 31/10/2012 01/11/2012
4.601 Loc1 23/10/2012 02/11/2012
4.601 Loc2 24/10/2012 27/10/2012
4.601 Loc2 25/10/2012 28/10/2012
4.602 Loc1 26/10/2012 29/10/2012
4.602 Loc1 27/10/2012 30/10/2012
4.603 Loc1 28/10/2012 31/10/2012
4.603 Loc2 29/10/2012 01/11/2012
4.603 Loc3 30/10/2012 02/11/2012
4.603 Loc4 31/10/2012 10/11/2012
4.604 Loc1 01/11/2012 11/11/2012
4.604 loc1 02/11/2012 12/11/2012

the current array formula is {=Min(IF(PID=RefNumber,IF(ActSheet=Ident,StartDates)))}
The formula looks up the Ref number, then looks up the Ident, matches them and then looks at the minimum date in the reurned values.
i.e If i wanted the earliest date for ref 4.600 with an ident of Loc2 it would return 27-10-12
The sheet contains hundreds of these and it really slows down the workbook.
Is there a less onerous way of doing this?
Thanks in advance...

Originally Posted by Ron Coderre

With your sample data in A1:D18

and
F1: a ref to match......4.6
G1: an ident to match...Loc2

This regular formula returns the minimum Start_date out of the records that match that criteria

Please Login or Register  to view this content.

In the above example, the formula returns: 10/27/2012

Is that something you can work with?

Hi Ron,
Thanks i will try - can I substitute ranges ok for A2:A20 etc?

cheers

That worked a treat Ron - If i substitute all my array formulas, will that speed things up do you think(i have about 1600 on this one sheet...)?
Thanks

Paul

I have modified all the formulae and it makes a significant difference, but still not 'zippy'.
I suspect that as I have built up this behemoth of a workbook, it has become somewhat 'lardy' with all the checks and balances built in.
I shall keep turning off the automatic calculation to give me that little extra when I am populating the sheet but at least it doesn't go comatose so often on me...
Thanks for your excellent solution, though I would like to know exactly what the 10^99 does within the formula if you have time to explain?