How to automatically generate the next logical number for a list in Excel

[theBIMman]

Hello,

I have a list of numbers, the list is not in order nor does the list always start with the same starting point. My need is to find the next logical number in the list (I've tried vlookup/max/index etc). The closest I got was a combination of max + 1 combined with vlookup. However, this applied the same next number to all blank cells when I'm wanting each new number to be added to the list before generating the next logical number.

Can anyone please give me some pointers? It would be greatly appreciated. A sample of the list is below.

Original List (must keep the alpha-numeric values in this list)	I can generate the new Alpha code using a LEFT formula from another column of data	I'd like to be able to get the next logical number for the blanks in this list. I'm happy to add the leading 00's in front of the real number and have tried value, max, vlookup but am stuck
	GAT
GAT001	GAT	001
GLA001	GLA	001
BLI001	BLI	001
BLI002	BLI	002
BLI004	BLI	004
BLI003	BLI	003
	BLI
BLI005	BLI	005
BLI006	BLI	006
	BLI
	BLI
CON001	CON	001
CUR001	CUR	001
	CUR

 

[jasonb75]

Is this what you need?

Lookup problem.xlsx
	A	B	C
1	Original List	Alpha code	next number
2		GAT	002
3	GAT001	GAT	001
4	GLA001	GLA	001
5	BLI001	BLI	001
6	BLI002	BLI	002
7	BLI004	BLI	004
8	BLI003	BLI	003
9		BLI	007
10	BLI005	BLI	005
11	BLI006	BLI	006
12		BLI	008
13		BLI	009
14	CON001	CON	001
15	CUR001	CUR	001
16		CUR	002
Sheet8

Cell Formulas
Range	Formula
C2:C16	C2	=IF(A2="",TEXT(COUNTIF(A:A,B2&"*")+COUNTIFS(A$2:A2,"",B$2:B2,B2),"000"),RIGHT(A2,3))