☰

允许包含多个分隔查找源值的单元格也包含非查找值

tag-icon • tag-icon microsoft-excel worksheet-function lookup
允许包含多个分隔查找源值的单元格也包含非查找值

给定一个包含多个分隔值的单元格，在不同的表中查找多个值

请打开链接查看之前回答的问题。

在这种情况下，是否存在一个公式可以直接返回位于列中的文本值E而不是执行查找？

如果列E包含数字和文本值的混合，公式是否可以通过查找返回与数字相关联的名称并直接返回文本值？

例子：

E3包含2;3;Johnny→F3公式结果B;C;Johnny

在此处输入图片描述

答案1

新的公式比原来的公式稍长，因为MID(…)必须复制该函数并再使用两次。

工作表截图

数组中输入（Ctrl++ Shift）Enter以下公式F2并将其复制粘贴/填充到其余列中：

{=
  TEXTJOIN(
  ";",
  TRUE,
  IF(
    ISNUMBER(
      --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
      )
    ),
    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
        )
      ))
    ),
    TRIM(
      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
      )
    )
  )
)}

请注意，公式中的变化只是添加了一个IF()函数，用于检查提取的值是数字还是文本，并以不同的方式处理它。文本值按原样返回，而数字值则像以前一样用于执行查找。


修改后的更简单的 Excel 2016（仅限 Windows）公式为：

{=TEXTJOIN(";",TRUE,IF(ISNUMBER(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b")),INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))),FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b")))}

相关内容