海鮮品都端出來了,
不得不將我的[家常菜]拿來示眾(可以的話, 也請修修):
數組:
=IF(E2="","",IF(OR(E2=E$2,$C$3:$C$15=E2),INDEX($B:$B,SMALL(IF($A$3:$A$15=E$2,ROW($3:$15),4^8),SUM(COUNTIF($C:$C,E$2:E2))+1)),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))&"")
或.
=LOOKUP("z",IF({1,0},"",IF(OR(E2=E$2,$C$3:$C$15=E2),VLOOKUP(E$2,OFFSET($A$1,IF(E2=E$2,,MATCH(E2,$C:$C,)),,99,3),2,),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))))
一般:
=LOOKUP("z",IF({1,0},"",IF(OR(E2=E$2,COUNTIF($C$3:$C$15,E2)),VLOOKUP(E$2,OFFSET($A$1,IF(E2=E$2,,MATCH(E2,$C:$C,)),,99,3),2,),LEFT(E2)&MID((1&MID(E2,2,9))+1,2,9))))
都是非常平易近人的口味, 不必翻鍋弄鼎的~~
|