Sommario
Per cercare i dati in base a più criteri complessi, è possibile utilizzare la funzione XLOOKUP con più espressioni basate sulla logica booleana. Nell'esempio mostrato, la formula in G5 è:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Con le impostazioni predefinite di XLOOKUP per la modalità di corrispondenza (esatta) e la modalità di ricerca (dal primo all'ultimo), la formula corrisponde al primo record in cui:
l'account inizia con "x" E la regione è "est" e il mese NON è aprile.
che è il quarto record (riga 8) nell'esempio mostrato.
Spiegazione
Normalmente, la funzione XLOOKUP è configurata per cercare un valore in una matrice di ricerca presente nel foglio di lavoro. Tuttavia, quando i criteri utilizzati per abbinare un valore diventano più complessi, è possibile utilizzare la logica booleana per creare al volo un array di ricerca composto solo da 1 e 0, quindi cercare il valore 1. Questo è l'approccio utilizzato in questo esempio:
=XLOOKUP(1,boolean_array,result_array)
In questo esempio, i criteri richiesti sono:
l'account inizia con "x" E la regione è "est" e il mese NON è aprile.
Per ciascuno dei tre criteri separati sopra, usiamo un'espressione logica separata. La prima espressione utilizza la funzione SINISTRA per verificare se Account inizia con "x":
LEFT(B5:B16)="x" // account begins with "x"
Poiché stiamo controllando dodici valori, il risultato è un array con dodici valori come questo:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
La seconda espressione verifica se Region è "east" utilizzando l'operatore uguale a (=):
C5:C16="east" // region is east
Come prima, otteniamo un altro array con dodici valori TRUE FALSE:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
La terza espressione deve escludere il mese di aprile. Il modo più semplice per farlo è provare per il mese di aprile direttamente con la funzione MESE:
MONTH(D5:D16)=4 // month is April
Quindi utilizzare la funzione NOT per invertire il risultato:
NOT(MONTH(D5:D16)=4) // month is not April
che crea un array che descrive correttamente "non aprile":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Successivamente, tutti e tre gli array vengono moltiplicati insieme e l'operazione matematica costringe i valori VERO e FALSO a 1 e 0:
(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)
Nell'aritmetica booleana, la moltiplicazione funziona come la funzione logica AND, quindi il risultato finale è un singolo array come questo:
(0;0;0;1;1;0;0;0;0;1;0;1)
La formula può ora essere riscritta in questo modo:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Con 1 come valore di ricerca e le impostazioni predefinite per la modalità di corrispondenza (esatta) e la modalità di ricerca (dal primo all'ultimo), XLOOKUP corrisponde al primo 1 (quarta posizione) e restituisce la riga corrispondente nella matrice dei risultati, che è B8: E8.
Ultima partita
Impostando l'argomento della modalità di ricerca opzionale su -1, puoi individuare l '"ultima corrispondenza" con gli stessi criteri in questo modo:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
