Fórmula genérica
=XLOOKUP("*"&value&"*",lookup,results,,2)
Resumo
Para usar XLOOKUP para corresponder valores que contêm texto específico, você pode usar curingas e concatenação. No exemplo mostrado, a fórmula em F5 é:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
onde código (B5: B15) e quantidade (C5: C15) são intervalos nomeados.
Explicação
A função XLOOKUP contém suporte integrado para curingas, mas esse recurso deve ser ativado explicitamente configurando o modo de correspondência para o número 2.
No exemplo mostrado, XLOOKUP é configurado para corresponder ao valor inserido na célula E5, que pode aparecer em qualquer lugar nos valores de pesquisa em B5: B15. A fórmula em F5 é:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, com asteriscos (*) concatenados na frente e atrás
- lookup_array - o código de intervalo nomeado (B5: B15)
- return_array - a quantidade do intervalo nomeado (C5: C15)
- if_not_found - a string "sem correspondência"
- match_mode - fornecido como 2 (correspondência curinga)
- search_mode - não fornecido. O padrão é 1 (primeiro ao último)
Para tornar uma correspondência de tipo "contém" automática, o asterisco curinga (*) é tanto prefixado quanto acrescentado ao valor na célula E5 com concatenação:
"*"&E5&"*"
Após a concatenação, a fórmula se torna:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP localiza a primeira correspondência que contém "BCC" (050-BCC-123 na linha 10) e retorna o valor correspondente da matriz de retorno, 50.
Observe que XLOOKUP não diferencia maiúsculas de minúsculas, inserir "bcc" em E5 retornará o mesmo resultado:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Veja abaixo uma opção para configurar o XLOOKUP para uma correspondência que diferencia maiúsculas de minúsculas.
Opção VLOOKUP
A fórmula VLOOKUP também oferece suporte a curingas quando definida para correspondência exata. A fórmula VLOOKUP equivalente para este exemplo é:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Explicação completa aqui.
Com SEARCH e FIND
Também é possível usar as funções SEARCH e FIND para realizar uma correspondência de tipo "contém" com XLOOKUP. Para uma correspondência que não diferencia maiúsculas de minúsculas (como o exemplo acima), você pode usar SEARCH assim:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Para uma correspondência que diferencia maiúsculas de minúsculas, você pode usar FIND em vez disso:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Ambas as opções acima tornam mais fácil estender os critérios para incluir outras condições usando a lógica booleana.
A lógica para ISNUMBER + SEARCH é explicada aqui.
Múltiplas correspondências
Se você precisar de várias correspondências, consulte a função FILTER.
