我有两张表,如下所示:
我希望 Excel 使用ID company associated
表 2(绿色)中的值,在表 1(橙色)中查找它并返回其中的值Company name
,或者当表 2 中的多个公司与同一个人相关联时返回多个值。
最好的解决办法是什么?
答案1
这是可以在 Excel 2016 中使用的公式。在早期版本的 Excel 中,TEXTJOIN()
需要 poly-fill UDF。(请参阅这个帖子为一个基本的。
数组中输入(Ctrl++ Shift)Enter以下公式F2
并将其复制粘贴/填充到其余列中:
{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}
请注意,此公式仅当列中的值A
实际存储为数字时才有效。对于文本值,--MID(…)
公式中的 需要替换为TRIM(MID(…))
。
美化后的公式如下:
{=
TEXTJOIN(
";",
TRUE,
INDEX(
(B:B),
N(IF(1,
MATCH(
--MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
),
(A:A),
0
)
))
)
)}
笔记:
- 美化的公式确实可以起作用。
- 美化版本中的括号
(A:A)
是为了强制A:A
保持在自己的一行上。 这同样适用于(B:B)
。
对于 Excel 2016(仅限 Windows),以下更简单的公式应该有效:
{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))))}
与上一个公式一样,此公式也仅适用于以数字形式存储的值。对于文本值,只需--
从公式中删除即可。