Offset & match

[watchouse]

Can I use OFFSET and MATCH to find the minimum value - see attached example.

Thanks,
Andrew

[DonkeyOte]

Solution will depend on whether or not it's a coincidence that the last value listed for each Serial is the smallest value ?

If it isn't - ie last record for any given Serial is always the smallest then you can use a LOOKUP construct else you're looking at an MIN Array*


*which pending data volumes might be calculation intensive and you might want to consider using an intermediate Pivot Table

[watchouse]

The records could be in any order. I know how to get the result using a pivot table and use lookup provided the data is sorted. Would really like a formula to do it in one go.

[DonkeyOte]

In which case you will need to use MIN Arrays - but be warned they are not efficient

Syntax:

=MIN(IF(criteria_range=criteria,value_range))
confirmed with CTRL + SHIFT + ENTER

(note confirmation keys - Enter alone will not suffice)

Given inherent inefficiencies it is in your interest to keep ranges as "lean" as possible ... pre XL2007 entire column references are not permitted.