Procura múltiplos valores em uma tabela diferente, dada uma célula contendo múltiplos valores delimitados

Question

Eu tenho duas tabelas, que são assim:

Gostaria que o Excel usasse o valor em ID company associated na tabela 2 (verde), procurasse na tabela 1 (laranja) e retornasse o valor de Company name , ou vários valores quando várias empresas estiverem associadas à mesma pessoa na tabela 2.

Qual seria a melhor solução?

    

Answer 1

Aqui está uma fórmula que funcionará no Excel 2016, como é. Nas versões anteriores do Excel, é necessário um UDF de preenchimento de polietileno para TEXTJOIN() . (Veja este post para um básico.)

Matrizentra(Ctrl+Desloca+Enter)aseguintefórmulaemF2ecopia-cola/fill-downnorestodacoluna:

{=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)))))}

Observe que essa fórmula só funciona se os valores na coluna A forem realmente armazenados como números. Para valores de texto, o --MID(…) na fórmula precisa ser substituído por TRIM(MID(…)) .

A fórmula prettificada é a seguinte:

{=
  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
      )
    ))
  )
)}

Notas:

• A fórmula prettificada realmente funciona se inserida.
• Os parênteses em torno de (A:A) na versão prettified são necessários para forçar o A:A a permanecer em sua própria linha. O mesmo se aplica para o (B:B) .

Para o Excel 2016 (somente Windows), a seguinte fórmula mais simples deve funcionar:

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

Assim como a fórmula anterior, esta também funciona apenas com valores armazenados como números. Para valores de texto, basta remover o -- da fórmula.