Come utilizzare CERCA.VERT con più criteri in Excel

Guarda il video - Come utilizzare la funzione CERCA.VERT con più criteri

La funzione CERCA.VERT di Excel, nella sua forma base, può cercare un valore di ricerca e restituire il valore corrispondente dalla riga specificata.

Ma spesso è necessario utilizzare VLOOKUP di Excel con più criteri.

Come utilizzare CERCA.VERT con più criteri

Supponiamo di avere un dato con il nome dello studente, il tipo di esame e il punteggio di matematica (come mostrato di seguito):

L'utilizzo della funzione CERCA.VERT per ottenere il punteggio di matematica per ogni studente per i rispettivi livelli di esame potrebbe essere una sfida.

Si può sostenere che un'opzione migliore sarebbe ristrutturare il set di dati o utilizzare una tabella pivot. Se funziona per te, niente del genere. Ma in molti casi, sei bloccato con i dati che hai e la tabella pivot potrebbe non essere un'opzione.

In questi casi, questo tutorial è per te.

Ora ci sono due modi per ottenere il valore di ricerca usando VLOOKUP con più criteri.

• Utilizzo di una colonna di supporto.
• Utilizzo della funzione SCEGLI.

CERCA.VERT con criteri multipli - Utilizzo di una colonna di supporto

Sono un fan delle colonne di supporto in Excel.

Trovo due vantaggi significativi nell'utilizzo delle colonne di supporto rispetto alle formule di matrice:

• Rende facile capire cosa sta succedendo nel foglio di lavoro.
• Lo rende più veloce rispetto alle funzioni array (notevole in grandi set di dati).

Ora, non fraintendermi. Non sono contrario alle formule di array. Adoro le cose incredibili che si possono fare con le formule di array. È solo che li conservo per occasioni speciali quando tutte le altre opzioni non sono di aiuto.

Tornando alla domanda in questione, la colonna helper è necessaria per creare un qualificatore univoco. Questo qualificatore univoco può quindi essere utilizzato per cercare il valore corretto. Ad esempio, ci sono tre Matt nei dati, ma c'è solo una combinazione di Matt e Unit Test o Matt e Mid-Term.

Ecco i passaggi:

• Inserisci una colonna di supporto tra le colonne B e C.
• Usa la seguente formula nella colonna helper:=A2&”|”&B2
  • Ciò creerebbe qualificatori univoci per ogni istanza come mostrato di seguito.
• Usa la seguente formula in G3 = CERCA.VERT($F3&”|”&G$2,$C$2:$D$19,2,0)
• Copia per tutte le celle.

Come funziona?

Creiamo qualificatori univoci per ogni istanza di un nome e dell'esame. Nella funzione CERCA.VERT utilizzata qui, il valore di ricerca è stato modificato in $F3&”|”&G$2 in modo che entrambi i criteri di ricerca vengano combinati e vengano utilizzati come un unico valore di ricerca. Ad esempio, il valore di ricerca per la funzione CERCA.VERT in G2 è Matt|Unit Test. Ora questo valore di ricerca viene utilizzato per ottenere il punteggio da C2: D19.

Chiarimenti:

Ci sono un paio di domande che probabilmente ti verranno in mente, quindi ho pensato di provare a rispondere qui:

• Perché ho usato | simbolo unendo i due criteri? - In alcune condizioni eccezionalmente rare (ma possibili), potresti avere due criteri che sono diversi ma finiscono per dare lo stesso risultato quando combinati. Ecco un esempio molto semplice (perdonami per la mia mancanza di creatività qui):

Nota che mentre A2 e A3 sono diversi e B2 e B3 sono diversi, le combinazioni finiscono per essere le stesse. Ma se usi un separatore, anche la combinazione sarebbe diversa (D2 e D3).

• Perché ho inserito la colonna helper tra le colonne B e C e non all'estrema sinistra? - Non c'è niente di male nell'inserire la colonna helper all'estrema sinistra. In effetti, se non vuoi temperare con i dati originali, quella dovrebbe essere la strada da percorrere. L'ho fatto perché mi fa usare meno numero di celle nella funzione CERCA.VERT. Invece di avere 4 colonne nell'array della tabella, potrei gestire solo 2 colonne. Ma questo sono solo io.

Ora non c'è una taglia che vada bene per tutti. Alcune persone potrebbero preferire non utilizzare alcuna colonna di supporto durante l'utilizzo di CERCA.VERT con più criteri.

Quindi ecco il metodo della colonna non di supporto per te.

Scarica il file di esempio

CERCA.VERT con criteri multipli - Utilizzo della funzione SCEGLI

L'utilizzo di formule di matrice anziché di colonne di supporto consente di risparmiare spazio sul foglio di lavoro e le prestazioni possono essere ugualmente buone se utilizzate meno volte in una cartella di lavoro.

Considerando lo stesso set di dati usato sopra, ecco la formula che ti darà il risultato:

=CERCA.VERT($E3&”|”&F$2,SCEGLI({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2, 0)

Poiché questa è una formula di matrice, usala con Ctrl + Maiusc + Invio, anziché solo Invio.

Come funziona?

La formula utilizza anche il concetto di colonna helper. La differenza è che invece di inserire la colonna helper nel foglio di lavoro, considerala come dati helper virtuali che fanno parte della formula.

Lascia che ti mostri cosa intendo per dati di supporto virtuale.

Nell'illustrazione sopra, mentre seleziono la parte SCEGLI della formula e premo F9, viene mostrato il risultato che la formula SCEGLI darebbe.

Il risultato è {“Matt|Unit Test”,91;”Bob|Unit Test”, 52;… }

È un array in cui una virgola rappresenta la cella successiva nella stessa riga e il punto e virgola rappresenta che i seguenti dati si trovano nella colonna successiva. Quindi, questa formula crea 2 colonne di dati: una colonna ha l'identificatore univoco e una ha il punteggio.

Ora, quando usi la funzione CERCA.VERT, cerca semplicemente il valore nella prima colonna (di questi dati di 2 colonne virtuali) e restituisce il punteggio corrispondente.

Scarica il file di esempio

Puoi anche utilizzare altre formule per eseguire una ricerca con più criteri (come INDEX/MATCH o SUMPRODUCT).

C'è un altro modo che conosci per farlo? Se sì, condividi con me nella sezione commenti.

Potrebbero piacerti anche i seguenti tutorial di LOOKUP:

• VLOOKUP Vs. INDICE/MATCH
• Ottieni più valori di ricerca senza ripetizione in una singola cella.
• Come rendere VLOOKUP distinzione tra maiuscole e minuscole.
• Usa IFERROR con VLOOKUP per sbarazzarti degli errori #N/A.