=SUMPRODUCT(ISNUMBER(SEARCH(C3,Certified))*ISNUMBER(SEARCH(D3,Certified)))
Onde Certified
é sua lista na coluna A.
Se a Sensibilidade ao caso ajudar (significando menos falsos positivos sem aumentar os falsos negativos), como seria para alguns exemplos apontados por @Scott em seu comentário abaixo, substitua FIND
por SEARCH
na fórmula.
=SUMPRODUCT(ISNUMBER(FIND(C3,Certified))*ISNUMBER(FIND(D3,Certified)))
Se você precisa garantir a correspondência de palavras inteiras, recomendamos uma solução VBA.
As funções ISNUMBER(..
retornam uma matriz de TRUE
ou FALSE
, dependendo se o Sobrenome ou o Primeiro Nome forem encontrados em uma linha.
=ISNUMBER(SEARCH(C3,Certified)) --> {FALSE;TRUE;FALSE;FALSE;FALSE}
=ISNUMBER(SEARCH(D3,Certified)) --> {TRUE;FALSE;FALSE;FALSE;FALSE}
Quando os dois arrays são multiplicados juntos, eles retornam um 1
para TRUE
no mesmo local (mesma linha) e 0
para qualquer outra combinação.
Acima, eles retornarão {0;0;0;0;0}
Para linha com Carl James
, vemos
{FALSE;FALSE;TRUE;FALSE;FALSE}
{FALSE;FALSE;TRUE;FALSE;FALSE}
que quando multiplicado - > {0;0;1;0;0}
Portanto, o SUMPRODUCT
retornará uma contagem do número de linhas que possuem o Sobrenome e o Primeiro Nome correspondentes à linha que está sendo testada na segunda tabela. Isso também será ativado se alguém tiver sido inserido mais de uma vez na sua primeira tabela.