Fórmula genérica
=FILTER(data,ISNUMBER(MATCH(rng1,rng2,0)),"No data")
Resumo
Para filtrar dados para incluir apenas registros onde uma coluna é igual a um de muitos valores, você pode usar a função FILTER junto com a função ISNUMBER e a função MATCH. No exemplo mostrado, a fórmula em F5 é:
=FILTER(data,ISNUMBER(MATCH(color,list,0)),"No data")
onde "dados" (B5: D15), "cor" (C5: C15) e "lista" (J5: J7) são intervalos nomeados.
Explicação
A função FILTER pode filtrar dados usando uma expressão lógica fornecida como o argumento "incluir". Neste exemplo, este argumento é criado com uma expressão que usa as funções ISNUMBER e MATCH como esta:
=ISNUMBER(MATCH(color,list,0))
MATCH é configurado para procurar cada cor em C5: C15 dentro da faixa menor J5: J7. A função MATCH retorna uma matriz como esta:
(1;#N/A;#N/A;#N/A;2;3;2;#N/A;#N/A;#N/A;3)
Os números dos avisos correspondem à posição das cores "encontradas" ("vermelho", "azul" ou "preto") e os erros correspondem às linhas em que uma cor de destino não foi encontrada. Para forçar um resultado TRUE ou FALSE, esta matriz vai para a função ISNUMBER, que retorna:
(TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
A matriz acima é entregue à função FLITER como o argumento de inclusão e FILTER retorna apenas as linhas que correspondem a um valor TRUE.
Com valores codificados permanentemente
O exemplo acima é criado com referências de células, em que as cores de destino inseridas no intervalo J5: J7. No entanto, usando uma constante de matriz, você pode codificar valores na fórmula como esta com o mesmo resultado:
=FILTER(data,ISNUMBER(MATCH(color,("red","blue","black"),0)),"No data")
