I hav a large array of data taking the format similar to below

Date Field Result
01/02/2008 Field 1 56
03/06/2007 Field 4 34
04/08/2008 Field 3 45
06/09/2008 Field 4 78
08/04/2007 Field 5 27
09/12/2006 Field 2 59

The dates are ongoing and a field may have a result covering one or more years and dates.
What I want to do is to enter Field 4 for example and get the latest result (78) for that.
This should also work if there is only one result for a field, and over the years there
may be three or four results for one field but I just want the latest. I thought MAX would
get the latest date but I cannot tie it together with the field name. Sorry dates are UK
format. I would value any guidance.

One way with an array formula that must be entered using ctrl+shift+enter
=INDEX(K:K,MATCH(MAX(IF($J$2:$J$22="field 4",$F$2:$I$22)),K:K))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Graham H" wrote in message
...
I hav a large array of data taking the format similar to below

Date Field Result
01/02/2008 Field 1 56
03/06/2007 Field 4 34
04/08/2008 Field 3 45
06/09/2008 Field 4 78
08/04/2007 Field 5 27
09/12/2006 Field 2 59

The dates are ongoing and a field may have a result covering one or more
years and dates. What I want to do is to enter Field 4 for example and get
the latest result (78) for that. This should also work if there is only
one result for a field, and over the years there may be three or four
results for one field but I just want the latest. I thought MAX would get
the latest date but I cannot tie it together with the field name. Sorry
dates are UK format. I would value any guidance.

Many thanks Don. I would never have worked that one out myself. Your help is much appreciated.

Graha

Don Guillett wrote:
One way with an array formula that must be entered using ctrl+shift+enter
=INDEX(K:K,MATCH(MAX(IF($J$2:$J$22="field 4",$F$2:$I$22)),K:K))

Try this *array* formula, with criteria (field 4) entered in D1:

=INDEX(C2:C7,MATCH(MAX((B2:B7=D1)*A2:A7),A2:A7,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Graham H" wrote in message
...
I hav a large array of data taking the format similar to below

Date Field Result
01/02/2008 Field 1 56
03/06/2007 Field 4 34
04/08/2008 Field 3 45
06/09/2008 Field 4 78
08/04/2007 Field 5 27
09/12/2006 Field 2 59

The dates are ongoing and a field may have a result covering one or more
years and dates.
What I want to do is to enter Field 4 for example and get the latest
result (78) for that.
This should also work if there is only one result for a field, and over
the years there
may be three or four results for one field but I just want the latest. I
thought MAX would
get the latest date but I cannot tie it together with the field name.
Sorry dates are UK
format. I would value any guidance.

Many thanks for that Ragdyer it fits the bill perfectly, much appreciated.

Ragdyer wrote:
Try this *array* formula, with criteria (field 4) entered in D1:

=INDEX(C2:C7,MATCH(MAX((B2:B7=D1)*A2:A7),A2:A7,0))

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Graham H" wrote in message
...
Many thanks for that Ragdyer it fits the bill perfectly, much appreciated.

Ragdyer wrote:
Try this *array* formula, with criteria (field 4) entered in D1:

=INDEX(C2:C7,MATCH(MAX((B2:B7=D1)*A2:A7),A2:A7,0))

If your dates are in ascending order then all you need to do if find the
last instance of the field and it will correspond to the latest date:

Dates in column A
Field in column B
Result in column C

E1 = Field 4

=LOOKUP(2,1/(B2:B7=E1),C2:C7)

--
Biff
Microsoft Excel MVP


"Graham H" wrote in message
...
I hav a large array of data taking the format similar to below

Date Field Result
01/02/2008 Field 1 56
03/06/2007 Field 4 34
04/08/2008 Field 3 45
06/09/2008 Field 4 78
08/04/2007 Field 5 27
09/12/2006 Field 2 59

The dates are ongoing and a field may have a result covering one or more
years and dates. What I want to do is to enter Field 4 for example and get
the latest result (78) for that. This should also work if there is only
one result for a field, and over the years there may be three or four
results for one field but I just want the latest. I thought MAX would get
the latest date but I cannot tie it together with the field name. Sorry
dates are UK format. I would value any guidance.

Many thanks for that approach which brings a different perspective on how to handle the
problem. Your help is much appreciated.

Graham

T. Valko wrote:
If your dates are in ascending order then all you need to do if find the
last instance of the field and it will correspond to the latest date:

Dates in column A
Field in column B
Result in column C

E1 = Field 4

=LOOKUP(2,1/(B2:B7=E1),C2:C7)

You're welcome!

--
Biff
Microsoft Excel MVP


"Graham H" wrote in message
...
Many thanks for that approach which brings a different perspective on how
to handle the problem. Your help is much appreciated.

Graham

T. Valko wrote:
If your dates are in ascending order then all you need to do if find the
last instance of the field and it will correspond to the latest date:

Dates in column A
Field in column B
Result in column C

E1 = Field 4

=LOOKUP(2,1/(B2:B7=E1),C2:C7)