Formula generica
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
Sommario
Per creare una corrispondenza esatta con distinzione tra maiuscole e minuscole, è possibile utilizzare la funzione XLOOKUP con la funzione EXACT. Nell'esempio mostrato, la formula in F5 è:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)
che corrisponde a "ROSSO" (distingue tra maiuscole e minuscole) e restituisce l'intera riga.
Spiegazione
Di per sé, la funzione XLOOKUP non fa distinzione tra maiuscole e minuscole. Un valore di ricerca di "RED" corrisponderà a "red", "RED" o "Red". Possiamo aggirare questa limitazione costruendo un array di ricerca adatto per XLOOKUP con un'espressione logica.
Lavorando dall'interno verso l'esterno, per dare a XLOOKUP la capacità di abbinare maiuscole e minuscole, usiamo la funzione ESATTO in questo modo:
EXACT(B5:B15,"RED") // test for "RED"
Poiché ci sono 11 valori nell'intervallo E5: D15, ESATTO restituisce un array con 11 risultati VERO FALSO come questo:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Notare che la posizione di TRUE corrisponde alla riga in cui il colore è "ROSSO".
Per brevità (e per permettere alla logica di essere facilmente estesa con la logica booleana), forziamo i valori VERO FALSO a 1 se 0 con il doppio negativo:
--EXACT(B5:B15,"RED") // convert to 1s and 0s
che produce un array come questo:
(0;0;0;0;1;0;0;0;0;0;0)
Notare che la posizione di 1 corrisponde alla riga in cui il colore è "ROSSO". Questa matrice viene restituita direttamente alla funzione XLOOKUP come argomento della matrice di ricerca.
Ora possiamo semplicemente la formula per:
=XLOOKUP(1,(0;0;0;0;1;0;0;0;0;0;0),B5:D15)
Con un valore di ricerca di 1, XLOOKUP trova l'1 nella quinta posizione e restituisce la quinta riga nella matrice di ritorno, B9: D9.
Estendere la logica
La struttura della logica può essere facilmente estesa. Ad esempio, per restringere la corrispondenza a "ROSSO" nel mese di aprile, puoi utilizzare una formula come questa:
=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)
In questo caso, poiché ciascuna delle due espressioni restituisce una matrice di valori VERO FALSO e poiché queste matrici vengono moltiplicate insieme, l'operazione matematica forza i valori VERO e FALSO su 1 e 0. Non è necessario utilizzare il doppio negativo.
Poiché il valore di ricerca rimane 1, come nella formula sopra.
Prima e ultima partita
Entrambe le formule precedenti restituiranno la prima corrispondenza di "ROSSO" in un set di dati. Se hai bisogno dell'ultima corrispondenza, puoi eseguire una ricerca inversa impostando l'argomento della modalità di ricerca per XLOOKUP su -1:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match
Se è necessario restituire i risultati di più corrispondenze, vedere la funzione FILTRO.
