Latest Result

[Graham H]

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.

 

[Don Guillett]

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))

 

[Ragdyer]

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.

 

[Graham H]

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

 

[Graham H]

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

Graha

 

[T. Valko]

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)

 

[Graham H]

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

Graham

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

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

Graham

 

[RagDyeR]

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