Разрешить ячейку, содержащую несколько исходных значений поиска с разделителями, также содержать значения без поиска

Question

Поиск нескольких значений в другой таблице по ячейке, содержащей несколько значений с разделителями

Пожалуйста, откройте ссылку, чтобы увидеть ранее отвеченный вопрос.

Учитывая этот контекст, есть ли формула, которая могла бы напрямую возвращать текстовое значение, расположенное в столбце E вместо выполнения поиска?

Если столбец E содержит комбинацию чисел и текстовых значений, может ли формула возвращать как имя, связанное с числом посредством поиска, так и текстовое значение напрямую?

Пример:

E3 содержит 2;3;Johnny → F3 результат формулы B;C;Johnny

Answer 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")))}