本帖最后由 cabcyvr 于 2017-9-21 07:21 编辑
假设新日期的数据范围在A11:E16 (上班时无聊写的,供参考)
G2=VLOOKUP(A2,$A$11:$E$16,IF(VLOOKUP(A2,$A$11:$E$16,5,)&""="",3,5),)
或
G2=IF(VLOOKUP(A2,$A$11:$E$16,5,)&""="",VLOOKUP(A2,$A$11:$E$16,3,),VLOOKUP(A2,$A$11:$E$16,5,))
或
G2=OFFSET($C$10,MATCH(A2,$A$11:$A$16,),IF(INDEX($E$11:$E$16,MATCH(A2,$A$11:$A$16,))&""="",0,2))
或
G2=VLOOKUP(A2,$A$11:$E$16,MIN(5,SUMPRODUCT(($A$11:$A$16=A2)*(RIGHT($B$10:$E$10,4)="生产日期")*($B$11:$E$16<>"")*(COLUMN($B$11:$E$16)))),)
或
G2=IF(HLOOKUP("新生产日期",$A$10:$E$16,MATCH(A2,$A$10:$A$16,),)&""="",HLOOKUP("生产日期",$A$10:$E$16,MATCH(A2,$A$10:$A$16,),),HLOOKUP("新生产日期",$A$10:$E$16,MATCH(A2,$A$10:$A$16,),))
或 (数组)
G2=OFFSET($A$10,MATCH(A2,$A$11:$A$16,),MAX(IF(($A$11:$A$16=A2)*(RIGHT($C$10:$E$10,2)="日期")*($C$11:$E$16<>""),COLUMN($C$11:$E$16)-1)))