E2单元格的数组公式 =IFERROR(TEXT(LARGE(IF(ISERROR(MATCH(A$2:A$24,B$2:B$18,0)),VALUE(A$2:A$24)),ROW(A1)),"000"),""),然后按Ctrl+Shift+Enter三键,下拉填充,如下图所示:
F2单元格的数组公式 =IFERROR(TEXT(LARGE(IF(ISERROR(MATCH(B$2:B$18,C$2:C$13,0)),VALUE(B$2:B$18)),ROW(B1)),"000"),""),然后按Ctrl+Shift+Enter三键,下拉填充,如下图所示: