編流水号 同类别 同年 (9550)

 

 C2=IF(OR(A2="",B2="",E2=""),"",VLOOKUP(B2,{"综合","ZH-";"项目","XM-";"生技","SJ-"},2,)&LEFT(E2,4)&TEXT(SUMPRODUCT((B$2:B2=B2)*(LEFT(E$2:E2,4)=LEFT(E2,4))),"-000"))