I'm using Excel 2003 to maintain a correspondence register. What I'd like to
be able to do is whenever a new piece of correspondence comes in, that when I
assign it a particular file reference, it automatically assigns the next
serial number in that particular sequence.

For example, if a letter comes in for Accounts, in column A I'd have the
date received, in B the overall file reference, then in C I would enter
"AC/". What I'd then want is for Excel to search all the instances of "AC/"
in column C in the rows above the new one, find the corresponding maximum
value in column D, then add 1 to it to give the next serial number.

So for the first instance, the cells would be:

21/3/07 123456/4/2/€¦ AC/ 27

and in the next row, when I enter

27/4/07 123456/4/2/€¦ AC/

I want "28" to appear in column D, and so on.

The closest I've come to a solution is below:

=IF($C3="","",1+VLOOKUP($C3,$C$2:$D2,2,FALSE))

but this doesn't find the maximum value, only the first value, and if I
remove the "FALSE" from the formula, then if I enter a different reference in
column C then it will take the next serial number in D regardless of what's
in C. Is there a way of manipulating VLOOKUP to this end, or is there
another combination of formulae I could try?