Cercare il valore più vicino con criteri multipli

In alcuni casi potrebbe essere necessario cercare il valore approssimativo o più vicino in base a più di un criterio. Combinando le funzioni INDICE, CONFRONTA e SE, puoi ottenere rapidamente il risultato desiderato in Excel.

Come cercare il valore più vicino in base a più criteri?

Come mostrato nello screenshot seguente, devi individuare la persona giusta per il lavoro in base a due criteri: «la laurea è in Informatica» e «l’esperienza lavorativa è di 15 anni».

Nota: affinché la formula funzioni correttamente, se sono presenti lauree duplicate, le relative esperienze lavorative devono essere ordinate in ordine crescente.

1. Seleziona una cella vuota per visualizzare il risultato, copia la formula seguente al suo interno e premi contemporaneamente i tasti Ctrl+Shift+Invio per ottenere il risultato.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Note:su questa formula:

• D3:D8è l'intervallo di colonne che contiene il risultato che stai cercando;
• G5contiene il secondo criterio (numero di anni di esperienza 15) in base al quale cercherai il valore;
• G4contiene il primo criterio (Computer) in base al quale cercherai il valore;
• B3:B8è l'intervallo di celle corrispondente al primo criterio;
• C3:C8è l'intervallo di celle corrispondente al secondo criterio;
• Numero 1indica una ricerca approssimativa, il che significa che se non viene trovato il valore esatto, verrà restituito il valore più grande inferiore al valore cercato;
• Questa formula deve essere inserita come formula di matrice utilizzando la combinazione di tasti Ctrl+Shift+Invio.

Funzionamento della formula

Questa formula può essere suddivisa nei seguenti componenti:

• IF(B3:B8=G4,C3:C8): la funzione SE restituisce qui il risultato {9;13;FALSE;FALSE;FALSE;FALSE}, ottenuto confrontando i valori nell'intervallo B3:B8 con il valore in G4. In caso di corrispondenza, restituisce il valore corrispondente da C3:C8; altrimenti, restituisce FALSO. In questo caso, si verificano due corrispondenze e quattro mancate corrispondenze.
• Una formula di matrice =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): la funzione CONFRONTA individua la posizione del numero 15 (il valore in G5) nell’intervallo C3:C8. Poiché il numero 15 non è presente, viene abbinato al valore immediatamente inferiore, ovvero 13. Il risultato in questo caso è quindi 2.
• E =INDEX(D3:D8,2): la funzione INDICE restituisce il valore della seconda cella nell’intervallo D3:D8. Il risultato finale è quindi Amy.

Funzioni correlate

Articoli correlati