Vendor list lookup and change if duplicate

[imeade]

I have a workbook that creates a vendorID based on a person's name and assigns an "01" at the end, this is in column A. When it creates the vendorID, I need it to check if the vendorID already exists in column G. If the vendorID already exists, then it would move on to the next number vendorID.

For example: "JOHNDOE01" is created, but then does a lookup to check if it exists, if it exists, then it would move to "JOHNDOE02" and it would continue to increase to JOHNDOE03, 04, etc.

I have tried vlookups with ifna, along with index/match, but this logic doesn't work to continue to increase the increment.

I thought I had figured it out via the formulas in cells A2 and A3, but unfortunately it doesn't work. The formulas in columns B and C were just trying out other things to no avail.

Thanks in advance for the help.

vendor.xlsx
	A	B	C	D	E	F	G
1	Vendor			PayeeName			Existing Vendors
2	JOHNDOE02	JOHNDOE01	JOHNDOE01	JOHN DOE			JOHNDOE01
3	BRIANHOPKI02	BRIANHOPKI02		BRIAN HOPKINS			JOHNDOE02
4							JOHNDOE03
5							JOHNDOE04
6							BRIANHOPKI01
7							BRIANHOPKI02
8							BRIANHOPKI03
9			JOHNDOE02				BRIANHOPKI04
10			JOHNDOE01
11
12
13
14
15
Lookup

Cell Formulas
Range	Formula
A2:A3	A2	=IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"01",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"01",LEFT(SUBSTITUTE(D2," ",""),10)&"02")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"02",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"02",LEFT(SUBSTITUTE(D2," ",""),10)&"03")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"03",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"03",LEFT(SUBSTITUTE(D2," ",""),10)&"04")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"04",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"04",LEFT(SUBSTITUTE(D2," ",""),10)&"05")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"05",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"05",LEFT(SUBSTITUTE(D2," ",""),10)&"06")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"06",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"06",LEFT(SUBSTITUTE(D2," ",""),10)&"07")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"07",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"07",LEFT(SUBSTITUTE(D2," ",""),10)&"08")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"08",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"08",LEFT(SUBSTITUTE(D2," ",""),10)&"09")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"09",G:G,1,FALSE)=LEFT(SUBSTITUTE(D2," ",""),10)&"09",LEFT(SUBSTITUTE(D2," ",""),10)&"10")),LEFT(SUBSTITUTE(D2," ",""),10)&"01")))))))))
B2	B2	=IFNA(VLOOKUP((LEFT(SUBSTITUTE(D2," ",""),10)&"01"),G:G,1,FALSE),"Not Found")
C2	C2	=IF(ISNA(INDEX(G:G,MATCH(B2,G:G,0),0)),"TESTING",INDEX(G:G,MATCH(B2,G:G,0),0))
B3	B3	=IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D3," ",""),10)&"01",G:G,1,FALSE)=LEFT(SUBSTITUTE(D3," ",""),10)&"01",LEFT(SUBSTITUTE(D3," ",""),10)&"02")),IFNA((IF(VLOOKUP(LEFT(SUBSTITUTE(D3," ",""),10)&"02",G:G,1,FALSE)=LEFT(SUBSTITUTE(D3," ",""),10)&"02",LEFT(SUBSTITUTE(D3," ",""),10)&"03")),LEFT(SUBSTITUTE(D3," ",""),10)&"01"))
C9	C9	=IF(LEFT(SUBSTITUTE(D2," ",""),10)&"01"=VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"01",G:G,1,FALSE),LEFT(SUBSTITUTE(D2," ",""),10)&"02","TESTING")
C10	C10	=VLOOKUP(LEFT(SUBSTITUTE(D2," ",""),10)&"01",G:G,1,FALSE)