数据多对多匹配的问题！

lizequn1976

求助：将sheet2中各城市对应的数值填写到本表中，如果数值不够用，则为空。简单的模拟数据，主要是完成多对多如何匹配。

你是那块小饼干

=LET(T,SCAN(0,A2:A15,LAMBDA(X,Y,COUNTIF(A2:Y,Y))),_T,SCAN(0,Sheet2!A2:A18,LAMBDA(_X,_Y,COUNTIF(Sheet2!A2:_Y,_Y))),IFNA(XLOOKUP(A2:A15&T,Sheet2!A2:A18&_T,Sheet2!B2:B18),""))

hugeinfo

=IFERROR(INDEX(Sheet2!B:B,MATCH(A2&COUNTIF($A$2:A2,A2),Sheet2!$A$2:$A$18&SCAN("",Sheet2!$A$2:$A$18,LAMBDA(x,y,COUNTIF(Sheet2!$A$2:y,y))),)+1),"")

zjnfxxh

=IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$A$2:$A$18=A2,ROW($2:$18)),COUNTIF($A$2:$A2,A2))),"")

leepoison

=IFERROR(MAP(A2:A15&MAP(A2:A15,LAMBDA(x,COUNTIF($A$2:x,x))),LAMBDA(m,FILTER(Sheet2!B2:B18,Sheet2!A2:A18&MAP(Sheet2!A2:A18,LAMBDA(x,COUNTIF(Sheet2!$A$2:x,x)))=m))),"")

mykitami

低版本
=IFNA(INDEX($B$2:$B$18,MATCH(F2&COUNTIF($F$2:F2,F2),$A$2:$A$18&COUNTIF(OFFSET($A$1,1,,ROW(INDIRECT("1:"&ROWS($A$2:$A$18)))),$A$2:$A$18),)),"")
高版本
=IFERROR(INDEX(FILTER($B$2:$B$18,$A$2:$A$18=F2),COUNTIF($F$2:F2,F2)),"")

mjm04

有些不明白楼主这么排是出于什么现实的意义呢