MAX IF not working???

[KateNash]

Hi

I have an array formula which is supposed to find the maximum code for a specific set of initials.

For example, I have codes such as

GRP1
GRP2
GRP3
SOA1
SOA2
SOA3

What I am trying to do is to automatically generate the next number in the sequence based on which group it belongs in - so if I put GRP in column C I want column D to populate the cell with GRP4 - as that's the next number in the sequence.

I thought I could do this with a lookup showing the MAX of those initials - using:

{=MAX(IF(criteria_range=criteria,values_range))}

but it doesn't seem to be working!

Any ideas or suggestions for other ways of doing this?

 

[Andrew Poulsom]

What's the actual formula that you are using? And what's in the referenced ranges?

 

[RoryA]

Is it always a three letter code? (you can't take a MAX of text values)

 

[Yahya]

try this

Excel Workbook
	A	B	C	D
1	GRP1
2	GRP2		GRP	GRP4
3	GRP3
4	SOA1
5	SOA2
6	SOA3
Sheet2

Excel 2007

Cell Formulas
Range	Formula
D2	=C2&COUNTIF(A1:A6,C2&"*")+1

 

[KateNash]

Thank you Yahya - that is a very simple but effective solution!!!

 

[RoryA]

If you will never have missing numbers, that is definitely the simplest!

 

[Yahya]

Thank you Yahya - that is a very simple but effective solution!!!

you are very welcome