Resumo
Para pesquisar dados com base em vários critérios complexos, você pode usar a função XLOOKUP com várias expressões com base na lógica booleana. No exemplo mostrado, a fórmula em G5 é:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Com as configurações padrão do XLOOKUP para modo de correspondência (exata) e modo de pesquisa (do primeiro ao último), a fórmula corresponde ao primeiro registro onde:
a conta começa com "x" E a região é "leste" e o mês NÃO é abril.
que é o quarto registro (linha 8) no exemplo mostrado.
Explicação
Normalmente, a função XLOOKUP é configurada para procurar um valor em uma matriz de pesquisa que existe na planilha. No entanto, quando os critérios usados para corresponder a um valor se tornam mais complexos, você pode usar a lógica booleana para criar uma matriz de pesquisa em tempo real composta apenas por 1s e 0s e, em seguida, procurar o valor 1. Esta é a abordagem usada neste exemplo:
=XLOOKUP(1,boolean_array,result_array)
Neste exemplo, os critérios exigidos são:
a conta começa com "x" E a região é "leste" e o mês NÃO é abril.
Para cada um dos três critérios separados acima, usamos uma expressão lógica separada. A primeira expressão usa a função LEFT para testar se a conta começa com "x":
LEFT(B5:B16)="x" // account begins with "x"
Como estamos verificando doze valores, o resultado é uma matriz com doze valores como este:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
A segunda expressão testa se Região é "leste" usando o operador igual a (=):
C5:C16="east" // region is east
Como antes, obtemos outra matriz com doze valores TRUE FALSE:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
A terceira expressão deve excluir o mês de abril. A maneira mais fácil de fazer isso é testar o mês de abril diretamente com a função MONTH:
MONTH(D5:D16)=4 // month is April
Em seguida, use a função NOT para reverter o resultado:
NOT(MONTH(D5:D16)=4) // month is not April
que cria uma matriz que descreve corretamente "não abril":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Em seguida, todas as três matrizes são multiplicadas juntas, e a operação matemática força os valores VERDADEIRO e FALSO para 1s e 0s:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Na aritmética booleana, a multiplicação funciona como a função lógica AND, então o resultado final é uma única matriz como esta:
(0;0;0;1;1;0;0;0;0;1;0;1)
A fórmula agora pode ser reescrita assim:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Com 1 como valor de pesquisa e configurações padrão para modo de correspondência (exata) e modo de pesquisa (do primeiro ao último), XLOOKUP corresponde ao primeiro 1 (quarta posição) e retorna a linha correspondente na matriz de resultados, que é B8: E8.
Última partida
Ao definir o argumento opcional do modo de pesquisa como -1, você pode localizar a "última correspondência" com os mesmos critérios como este:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
